package com.sxfq.loansupermarket.backsystem.modular.arrival.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Medium;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.User;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.ApprovalProcessInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.SearchCustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.UserInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.system.service.IDeptService;
import com.sxfq.loansupermarket.backsystem.modular.system.transfer.UserDto;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.AddVisitedInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.ConsultantInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.*;
import java.net.URLEncoder;
import java.security.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * 预约客户信息
 *
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/customer")
public class CustomerController extends BaseController {

    @Autowired
    ICustomerService service;

    @Autowired
    IConsultantService consultantService;

    @Autowired
    private SqlMapper sqlMapper;

    @Autowired
    IReservationsService reservationsService;

    @Autowired
    IDeptService deptService;

    private String PREFIX = "/arrival/customer/";

    private String PREFIY = "/tcCenter/consultant/";

    /**
     * 跳转到预约客户管理页面
     */
    @RequestMapping("")
    public String customer(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "/customer.html";
    }

    /**
     * 跳转预约视图
     */
    @RequestMapping("/customer_reservationView")
    public String customer_reservationView(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "customer_reservationViews.html";
    }

    @RequestMapping(value = "/to_regresses/{idName}")
    public String to_openBileListss(@PathVariable String idName, Model model) throws Exception {
        if (ToolUtil.isEmpty(idName)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String customerId = idName.split(",")[0];
        String customerName = idName.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select id as id,reservations_id as reservationsId, medical_record_number as medicalRecordNumber, arrival_store as arrivalStore,customer_name as customerName,customer_age as customerAge," +
                " customer_phone as customerPhone ,  " +
                " (\n" +
                "        CASE customer_sex\n" +
                "        WHEN 1 THEN\n" +
                "        '男'\n" +
                "        WHEN 2 THEN\n" +
                "        '女'\n" +
                "        ELSE\n" +
                "        '未知'\n" +
                "        END\n" +
                "        ) as sex  from " + this.getTableName() + " where id='" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        if (StringUtil.isEmpty(customerInfo.getMedicalRecordNumber())) {
            customerInfo.setMedicalRecordNumber(service.getMedicalRecordNumber());
        }
        model.addAttribute("customerId", customerId);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("customerName", customerName);
        return PREFIX + "enter_appointment_number.html";
    }


    /**
     * 跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/to_nature_courtyard_add/{customerPhone}")
    public String to_nature_courtyard_add(@PathVariable String customerPhone, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from reservations_info where customer_phone='" + customerPhone + "' and status='到店'";
        List<ReservationsInfo> reservationsInfo = sqlMapper.selectList(sql, ReservationsInfo.class);
        if(reservationsInfo.size()>0){
            model.addAttribute("customerStatus", "复到");
        }else{
            model.addAttribute("customerStatus", "初到");
        }

        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerPhone", customerPhone);
        model.addAttribute("medicalRecordNumber", service.getMedicalRecordNumber());
        return PREFIX + "nature_courtyard_add.html";
    }

    /**
     * 跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/to_nature_courtyard_addd/{customerPhone}")
    public String to_nature_courtyard_addd(@PathVariable String customerPhone, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from reservations_info where customer_phone='" + customerPhone + "' and status='到店'";
        List<ReservationsInfo> reservationsInfo = sqlMapper.selectList(sql, ReservationsInfo.class);
        if(reservationsInfo.size()>0){
            model.addAttribute("customerStatus", "复到");
        }else{
            model.addAttribute("customerStatus", "初到");
        }

        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerPhone", customerPhone);
        model.addAttribute("medicalRecordNumber", service.getMedicalRecordNumber());
        return PREFIX + "nature_courtyard_addd.html";
    }

    /**
     * 医生页面跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/to_nature_courtyard_adds")
    public String to_nature_courtyard_adds() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "nature_courtyard_adds.html";
    }

    /**
     * 医生页面跳转到修改患者信息添加页面
     */
    @RequestMapping(value = "/to_nature_courtyard_update/{customerId}")
    public String to_nature_courtyard_update(@PathVariable String customerId, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from  "+this.getTableName()+" t" +
                " where t.id='" + customerId + "' limit 0,1 ";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        model.addAttribute("customerInfo", customerInfo);
        return PREFIX + "nature_courtyard_update.html";
    }


    /**
     * 修改患者信息
     */
    @RequestMapping(value = "/updateCustomerInfoOk/{reservationsId}")
    @ResponseBody
    public String updateCustomerInfoOk(@PathVariable String reservationsId, @Valid CustomerInfo info, Model model) {
        //查询已有手机号的预约集合
        List<ReservationsInfo> existList = reservationsService.getCustomerPhoneCounts(info.getCustomerPhone());
        /*int count = existList.size();
        if (!"本人".equals(info.getPhoneSharShip())) {
            info.setCustomerPhone(info.getCustomerPhone() + "+" + count);
        }*/
        if (StringUtil.isEmpty(info.getCustomerAge())) {
            info.setCustomerAge(0);
        }
        String rt = "";
        String sql1 = "update reservations_info set customer_name='" + info.getCustomerName() + "'," +
                "customer_age='" + info.getCustomerAge() + "',one_channel='" + info.getOneChannel() + "'," +
                "one_medium='" + info.getOneMedium() + "',province='" + info.getProvince() + "'," +
                "customer_phone='" + info.getCustomerPhone() + "',customer_sex='" + info.getCustomerSex() + "'," +
                "two_channel='" + info.getTwoChannel() + "',two_medium='" + info.getTwoMedium() + "'," +
                "cities='" + info.getCities() + //"',phone_shar_ship='" + info.getPhoneSharShip() +
                "', thr_channel='" + info.getThrChannel() + "'," +
                "thr_medium='" + info.getThrMedium() + "',region='" + info.getRegion() + "' " +
                " where id='" + reservationsId + "'  ";
        rt = "" + sqlMapper.update(sql1) + "," + reservationsId;
//        String ursql = "INSERT INTO `reservations_update_info` (`update_people`, `update_time`, `update_cname`, `update_cphone`,`archiving`,`filing_time`) VALUES " +
//                "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + info.getArchiving() + "', " + info.getFilingTime() + ");";
//        sqlMapper.insert(ursql);
        String sql2 = "select * from  " + this.getTableName() + "  where reservations_id='" + reservationsId + "'  ";
        List<CustomerInfo> customerInfo = sqlMapper.selectList(sql2, CustomerInfo.class);
        if (customerInfo.size() > 0) {
            if (info.getBirthday() == "" || "".equals(info.getBirthday())) {
                info.setBirthday("null");
            }
            String sql = "update " + this.getTableName() + " set customer_name='" + info.getCustomerName() + "'," +
                    "customer_age='" + info.getCustomerAge() + "',one_channel='" + info.getOneChannel() + "'," +
                    "one_medium='" + info.getOneMedium() + "',province='" + info.getProvince() + "'," +
                    "customer_phone='" + info.getCustomerPhone() + "',customer_sex='" + info.getCustomerSex() + "'," +
                    "two_channel='" + info.getTwoChannel() + "',two_medium='" + info.getTwoMedium() + "'," +
                    "cities='" + info.getCities() + //"',phone_shar_ship='" + info.getPhoneSharShip() +
                    "',customer_status='" + info.getCustomerStatus() + "'," +
                    "birthday= " + info.getBirthday() + " ,thr_channel='" + info.getThrChannel() + "'," +
                    "thr_medium='" + info.getThrMedium() + "',region='" + info.getRegion() + "' " +
                    " where reservations_id='" + reservationsId + "'  ";
            rt = sqlMapper.update(sql) + "," + reservationsId;
        }
        return rt;
    }


    /**
     * 跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/customer_add/{id}")
    public String customerAdds(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        ReservationsInfo reservationsInfo = reservationsService.selectOneById(id);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("reservationsId", reservationsInfo.getId());
        model.addAttribute("customerPhone", reservationsInfo.getCustomerPhone());
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("medicalRecordNumber", service.getMedicalRecordNumber());

//        String sql = "select * from reservations_info where customer_phone='" + reservationsInfo.getCustomerPhone() + "' and status='到店'";
//        List<ReservationsInfo> reservationsInfo2 = sqlMapper.selectList(sql, ReservationsInfo.class);
//        if(reservationsInfo2.size()>0){
//            model.addAttribute("customerStatus", "复到");
//        }else{
            model.addAttribute("customerStatus", "初到");
//        }
        return PREFIX + "customer_add.html";
    }

    @RequestMapping(value = "/customer_addd/{id}")
    public String customerAddsd(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        ReservationsInfo reservationsInfo = reservationsService.selectOneById(id);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("reservationsId", reservationsInfo.getId());
        model.addAttribute("customerPhone", reservationsInfo.getCustomerPhone());
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("medicalRecordNumber", service.getMedicalRecordNumber());

        String sql = "select * from reservations_info where customer_phone='" + reservationsInfo.getCustomerPhone() + "' and status='到店'";
        List<ReservationsInfo> reservationsInfo2 = sqlMapper.selectList(sql, ReservationsInfo.class);
        if(reservationsInfo2.size()>0){
            model.addAttribute("customerStatus", "复到");
        }else{
            model.addAttribute("customerStatus", "初到");
        }

        return PREFIX + "customer_addd.html";
    }

    /**
     * 跳转到预约客户管理详情页面
     */
    @RequestMapping(value = "/customer_update/{customerId}")
    public String customerUpdate(@PathVariable String customerId, Model model) {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        CustomerInfo customerInfo = this.service.selectOneById(customerId);
        String selectsql = "select " +
                "  (CASE a.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,a.* from reservations_info a where id= '" + customerInfo.getReservationsId() + "' limit 0,1";
        ReservationsInfo reservations = sqlMapper.selectOne(selectsql, ReservationsInfo.class);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("reservations", reservations);
        return PREFIX + "customer_edit.html";
    }

    /**
     * 跳转到预约客户管理详情页面
     */
    @RequestMapping(value = "/customer_details/{customerId}")
    public String customerDetails(@PathVariable String customerId, Model model) {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        CustomerInfo customerInfo = this.service.selectOneById(customerId);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerInfo", customerInfo);
        return PREFIX + "customer_details.html";
    }

    @RequestMapping(value = "/openToVoid/{customerId}")
    public String openToVoid(@PathVariable String customerId, Model model) {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        CustomerInfo customerInfo = this.service.selectOneById(customerId);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerInfo", customerInfo);
        return PREFIX + "to_void.html";
    }

    @RequestMapping(value = "/openRevisit/{reservationsId}")
    public String openRevisit(@PathVariable String reservationsId, Model model) {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        CustomerInfo customerInfo = this.service.selectOneByReservationsId(reservationsId);
        model.addAttribute("customerInfo", customerInfo);
        return PREFIY + "openRevisit.html";
    }

    /**
     * 选择TC
     */
    @RequestMapping(value = "/to_choiceTc/{id}")
    public String choiceTc(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("customerId", id);
        return PREFIY + "customer_choiceTc.html";
    }

    /**
     * 选择客户状态
     */
    @RequestMapping(value = "/to_choice_status/{id}")
    public String toChoiceStatus(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("customerId", id);
        return PREFIY + "customer_choice_customerstatus.html";
    }

    /**
     * 选择客户状态
     */
    @RequestMapping(value = "/to_choice_statusx/{id}")
    public String toChoiceStatusx(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("customerId", id);
        return PREFIY + "customer_choice_customerstatusx.html";
    }

    @RequestMapping(value = "/to_choiceAppointment/{id}")
    public String choiceAppointment(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("customerId", id);
        return PREFIY + "customer_choiceAppointment.html";
    }

    @RequestMapping(value = "/choiceTc", method = RequestMethod.POST)
    @ResponseBody
    public int choiceTc(String customerId, String tcName) throws Exception {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sq1 = "update " + this.getTableName() + " a set a.field_tc='" + tcName + "' where id='" + customerId + "'";
        return sqlMapper.update(sq1);
    }

    /**
     * 客户状态请选择
     *
     * @param customerId
     * @param customerStatus
     */
    @RequestMapping(value = "/choiceCustomerStatus", method = RequestMethod.POST)
    @ResponseBody
    public int choiceCustomerStatus(String customerId, String remarks, String customerStatus) throws Exception {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql3 = "select id as id ,customer_name as customerName,customer_phone as customerPhone, " +
                " customer_status as customerStatus from " + this.getTableName() + " where id='" + customerId + "'";
        CustomerInfo info = sqlMapper.selectOne(sql3, CustomerInfo.class);
        String sq1 = "update " + this.getTableName() + " a set a.customer_status='" + customerStatus + "' where id='" + customerId + "'";
        String sq11 = "INSERT INTO `customer_status_update_log` (`logtype`, `userid`, `customer_status`, `customer_name`,`customer_phone`,`single_person`, `createtime`,`succeed`, `message`,`classname`,`remarks`) VALUES " +
                " ( '修改'," + ShiroKit.getUser().getId() + ",'" + customerStatus + "', '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'成功','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + " 把 " + info.getCustomerName() + " 的客户状态:" + info.getCustomerStatus() + " 修改为:" + customerStatus + "','" + remarks + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sq1);
    }

    /**
     * 客户状态请选择
     *
     * @param customerId
     */
    @RequestMapping(value = "/xchoiceCustomerStatus", method = RequestMethod.POST)
    @ResponseBody
    public int xchoiceCustomerStatus(String customerId, String remarks, String xcustomerStatus) throws Exception {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql3 = "select id as id ,customer_name as customerName,customer_phone as customerPhone, " +
                " customer_status as customerStatus,xcustomer_status as xcustomerStatus from " + this.getTableName() + " where id='" + customerId + "'";
        CustomerInfo info = sqlMapper.selectOne(sql3, CustomerInfo.class);
        String sq1 = "update " + this.getTableName() + " a set a.xcustomer_status='" + xcustomerStatus + "' where id='" + customerId + "'";
        String sq11 = "INSERT INTO `customer_status_update_log` (`logtype`, `userid`, `customer_status`, `customer_name`,`customer_phone`,`single_person`, `createtime`,`succeed`, `message`,`classname`,`remarks`) VALUES " +
                " ( '修改'," + ShiroKit.getUser().getId() + ",'" + xcustomerStatus + "', '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'成功','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + " 把 " + info.getCustomerName() + " 的客户状态:" + info.getXcustomerStatus() + " 修改为:" + xcustomerStatus + "','" + remarks + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sq1);
    }

    @RequestMapping(value = "/choiceAppointment", method = RequestMethod.POST)
    @ResponseBody
    public int choiceAppointment(String customerId, String appointmentPayService) throws Exception {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sq1 = "update " + this.getTableName() + " a set a.appointment_pay_service='" + appointmentPayService + "' where id='" + customerId + "'";
        return sqlMapper.update(sq1);
    }

//    @RequestMapping(value = "/to_reservation_matching/{customerPhone}")
//    public String to_reservation_matching(@PathVariable String customerPhone, Model model) {
//        if (ToolUtil.isEmpty(customerPhone)) {
//            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
//        }
//        String tableHead = ShiroKit.getUser().getAbbreviation();
//        if (tableHead == "" || tableHead == null) {
//            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
//            ShiroKit.getSubject().logout();
//            return "/login.html";
//        }
//        List<CustomerInfo> customerInfoList = this.service.selectOneByCp(customerPhone);
//        List<ReservationsInfo> reservationsInfoList = this.reservationsService.selectOneByCp(customerInfoList, customerPhone);
//        model.addAttribute("reservationsInfoList", reservationsInfoList);
//        model.addAttribute("customerInfoList", customerInfoList);
//        model.addAttribute("customerPhone", customerPhone);
//        if (reservationsInfoList.size() == 0 && customerInfoList.size() == 0) {
//            return PREFIX + "nature_courtyard.html";
//        } else {
//            return PREFIX + "reservation_matching.html";
//        }
//    }

    @RequestMapping(value = "/to_reservation_matching/{customerPhone}")
    public String to_reservation_matching(@PathVariable String customerPhone, Model model) {
        String phone= customerPhone.trim();
        if (ToolUtil.isEmpty(phone)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        //List<CustomerInfo> customerInfoList = this.service.selectOneByCp(customerPhone);
        // List<ReservationsInfo> reservationsInfoList = this.reservationsService.selectOneByCp(customerInfoList, customerPhone);

        //查询到店的患者
        String phoneSql1=" select * from (select if(a.customer_sex='1','男','女') as sex,a.* from "+this.getTableName()+" a where customer_phone like '%" + phone + "%' " +
                "  order by arrival_time desc ) t group by t.customer_phone";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(phoneSql1,CustomerInfo.class);

        //查询市场患者
        List<ReservationsInfo> reservationsInfo=new ArrayList<>();
        if(customerInfoList.size() == 0){
            String phoneSql3="select if(a.customer_sex='1','男','女') as sex,a.* from reservations_info a " +
                    " where customer_phone like '%" + phone + "%' " +
                    " and (arrival_store !=(select simplename from dept where abbreviation='"+tableHead+"' ) or arrival_store is null) ";
            reservationsInfo = sqlMapper.selectList(phoneSql3,ReservationsInfo.class);
            model.addAttribute("reservationsInfoList", reservationsInfo);
        }else{
            //查询同手机号未到店市场建档的患者
            String reservationsId="";

            //查询到店的患者
            String sql1="  select * from (select if(a.customer_sex='1','男','女') as sex,a.* from "+this.getTableName()+" a where customer_phone like '%" + phone + "%' " +
                    " and status='到店' order by arrival_time desc ) t group by t.customer_phone " ;
            List<CustomerInfo> customerInfoList2 = sqlMapper.selectList(sql1,CustomerInfo.class);
            for (CustomerInfo resInfo : customerInfoList2) {
                reservationsId += "'"+resInfo.getReservationsId() + "',";
            }
            String phoneSql2="select if(a.customer_sex='1','男','女') as sex,a.* from reservations_info a where customer_phone like '%" + phone + "%' " +
                    " and id not in ("+reservationsId.substring(0,reservationsId.length()-1)+")" +
                    " and (arrival_store !=(select simplename from dept where abbreviation='"+tableHead+"' ) or arrival_store is null) ";
            reservationsInfo = sqlMapper.selectList(phoneSql2,ReservationsInfo.class);
            if(reservationsInfo!=null){
                model.addAttribute("reservationsInfoList", reservationsInfo);
            }
        }
        model.addAttribute("customerInfoList", customerInfoList);
        model.addAttribute("customerPhone", phone);

        if (customerInfoList.size() == 0 && reservationsInfo.size()==0 ) {
            return PREFIX + "nature_courtyard.html";
        } else {
            return PREFIX + "reservation_matching.html";
        }
    }

    @RequestMapping(value = "/to_reservation_matchings/{customerPhone}")
    public String to_reservation_matchings(@PathVariable String customerPhone, Model model) {
        if (ToolUtil.isEmpty(customerPhone)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String phoneSql1="select * from "+this.getTableName()+" where customer_phone like '%" + customerPhone + "%' order by arrival_time desc limit 0,1 ";
        List<ReservationsInfo> reservationsInfoList1 = sqlMapper.selectList(phoneSql1,ReservationsInfo.class);
        if(reservationsInfoList1.size() == 0){
            String phoneSql2="select * from reservations_info where customer_phone like '%" + customerPhone + "%'";
            reservationsInfoList1 = sqlMapper.selectList(phoneSql2,ReservationsInfo.class);
        }
        model.addAttribute("reservationsInfoList", reservationsInfoList1);
        //model.addAttribute("customerInfoList", customerInfoList);
        model.addAttribute("customerPhone", customerPhone);
        if (reservationsInfoList1.size() == 0 ) {
            return PREFIX + "nature_courtyards.html";
        } else {
            return PREFIX + "reservation_matchings.html";
        }
    }

    @RequestMapping(value = "/openNoRevisit/{reservationsId}")
    public String openNoRevisit(@PathVariable String reservationsId, Model model) {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        CustomerInfo customerInfo = this.service.selectOneByReservationsId(reservationsId);
        model.addAttribute("customerInfo", customerInfo);
        return PREFIY + "openNoRevisit.html";
    }

    /**
     * 跳转到预约客户管理详情页面
     */
    @RequestMapping(value = "/reconsulting/{customerId}")
    @ResponseBody
    public int reconsulting(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (service.getUpdatePhoneCount(info.getCustomerPhone(), info.getId()).size() > 0) {
            return 0;
        } else {
            service.update(info);
            return 1;
        }
    }

    @RequestMapping(value = "/to_reconsulting/{id}")
    public String to_reconsulting(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        //获取登录人部门信息
        Integer deptId = ShiroKit.getUser().getDeptId();
        Dept dept = deptService.selectDeptById(deptId);
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }

        ReservationsInfo info = reservationsService.selectOneById(id);
        CustomerInfo customerInfo=new CustomerInfo();
        if(info==null){   //复到传过来的为客户id
            String sql = "select * from "+this.getTableName()+" where id='" + id + "' order by arrival_time desc limit 0,1 ";
            customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
            model.addAttribute("customerStatus", "复到");
        }else{     //初到过来的为预约id
            String sql3="select * from reservations_info where id='"+id+"' order by first_visit_date DESC limit 0,1";
            customerInfo = sqlMapper.selectOne(sql3,CustomerInfo.class);
            model.addAttribute("customerStatus", "初到");
        }
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("dept", dept);
        model.addAttribute("customerInfo", customerInfo);
        return PREFIX + "reconsulting.html";
    }

    @RequestMapping(value = "/to_reconsultingd/{id}")
    public String to_reconsultingd(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        CustomerInfo customerInfo = this.service.selectOneById(id);
        customerInfo.setCustomerStatus(this.service.getCustomerStatus(customerInfo));
//
//        String sql = "select * from reservations_info where customer_phone='" + customerInfo.getCustomerPhone() + "' and status='到店'";
//        List<ReservationsInfo> reservationsInfo = sqlMapper.selectList(sql, ReservationsInfo.class);
//        if(reservationsInfo.size()>0){
            model.addAttribute("customerStatus", "复到");
//        }else{
//            model.addAttribute("customerStatus", "初到");
//        }

        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerInfo", customerInfo);
        return PREFIX + "reconsultingd.html";
    }

    @RequestMapping(value = "/getDepartment", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServiceInfo> getDepartment() {
        List<PayServiceInfo> payServiceInfoList = consultantService.getPayServiceInfoList();
        return payServiceInfoList;
    }


    @RequestMapping(value = "/toVoidDetails/{id}")
    public String toVoidDetails(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        CustomerInfo customerInfo = this.service.selectOneById(id);
        customerInfo.setCustomerStatus(this.service.getCustomerStatus(customerInfo));
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerInfo", customerInfo);
        return PREFIX + "void_details.html";
    }

    /**
     * 预约客户管理列表
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object testrInfoList(SearchCustomerInfo info, HttpServletRequest request) {
        Page<CustomerInfo> page = new PageFactory<CustomerInfo>().defaultPage();
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<CustomerInfo> list = service.selectPageListByQueryGetMap(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = service.selectPageListByQueryGetMap(page, info);
            }
            page.setTotal(service.getQueryPagesCount(page, info));
            page.setRecords(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 市场到前台修改渠道提交
     */
    @RequestMapping(value = "/update_channel", method = RequestMethod.POST)
    @ResponseBody
    public int updateChannel(String name, String sOneChannel, String reservationsId,
                             String sTwoChannel, String sThrChannel, String oneChannel, String outpatientName,String user,
                             String twoChannel, String thrChannel, String remarks) throws Exception {

        String id = CommUtils.getUUID();
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        String sql = "INSERT INTO  approval_process_info (id,reservations_id,`name`,`one_channel`,`two_channel`,`thr_channel`,`s_one_channel`,`s_two_channel`,`s_thr_channel`," +
                " `operation_time`, `remarks`,outpatient_name,user) VALUES" +
                "('" + id + "','" + reservationsId + "','" + name + "','" + oneChannel + "','" + twoChannel + "','" + thrChannel + "'," +
                "'" + sOneChannel + "','" + sTwoChannel + "','" + sThrChannel + "','" + DateTimeKit.now() + "','" + remarks + "','" + outpatientName + "','"+user+"');";
        return sqlMapper.insert(sql);
    }

    /**
     * 前台到市场修改渠道提交
     */
    @RequestMapping(value = "/update_channel1", method = RequestMethod.POST)
    @ResponseBody
    public int updateChannel2(String name, String sOneChannel, String reservationsId,
                              String sTwoChannel, String sThrChannel, String oneChannel, String archiving,
                              String twoChannel, String thrChannel, String remarks) throws Exception {

        String id = CommUtils.getUUID();
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        String sql = "INSERT INTO  approval_process_info (id,reservations_id,archiving,`name`,`one_channel`,`two_channel`,`thr_channel`,`s_one_channel`,`s_two_channel`,`s_thr_channel`," +
                " `operation_time`, `remarks`) VALUES" +
                "('" + id + "','" + reservationsId + "','" + archiving + "','" + name + "','" + oneChannel + "','" + twoChannel + "','" + thrChannel + "'," +
                "'" + sOneChannel + "','" + sTwoChannel + "','" + sThrChannel + "','" + DateTimeKit.now() + "','" + remarks + "');";
        return sqlMapper.insert(sql);
    }

    /**
     * 添加到店客户
     */
    @RequestMapping(value = "/add")
    @ResponseBody
    public int add(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
//        reservationsService.updateOneChannle();
        String sql = "select * from " + this.getTableName() + " a where a.customer_name='" + info.getCustomerName() + "'\n" +
                "and a.customer_phone='" + info.getCustomerPhone() + "' and \n" +
                "left(a.arrival_time,10)=left(curdate(),10) and customer_status!='作废'";
        List<CustomerInfo> list = sqlMapper.selectList(sql, CustomerInfo.class);
        if (list.size() > 0) {
            return 0;
        } else {
            service.insert(info);
            String selectsql = "select * from reservations_info where id='" + info.getReservationsId() + "' limit 0,1";
            ReservationsInfo reservations = sqlMapper.selectOne(selectsql, ReservationsInfo.class);
            if (reservations != null) {
                if (StringUtil.isEmpty(reservations.getFirstVisitDate())) {
                    String sql1 = "update reservations_info set status='到店',outpatient_name='"+info.getArrivalStore()+"',filling_status='到店沉淀期',tc_name='" + info.getFieldTc() + "',first_visit_date='" + DateTimeKit.now() + "'," +
                            "identification_number='" + info.getIdentificationNumber() + "',arrival_store='" + ShiroKit.getUser().getOutpatientName() + "' where id = '" + info.getReservationsId() + "'";
                    sqlMapper.update(sql1);
                } else {
                    String sql1 = "update reservations_info set status='到店',outpatient_name='"+info.getArrivalStore()+"',filling_status='到店沉淀期',tc_name='" + info.getFieldTc() + "',identification_number='" + info.getIdentificationNumber() + "',arrival_store='" + ShiroKit.getUser().getOutpatientName() + "'  where id = '" + info.getReservationsId() + "'";
                    sqlMapper.update(sql1);
                }
            }
            //   service.updateReservation(info.getReservationsId());
            return 1;
        }
    }

    /**
     * 添加到店客户
     */
    @RequestMapping(value = "/addNatureCustomer")
    @ResponseBody
    public String addCustomer(@Valid CustomerInfo info, BindingResult result, Model model) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String id = "";
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        if (info.getReferee() != null && info.getReferee() != "") {
            id = "老带新" + CommUtils.getUUID() + randomNumber;
            info.setOneChannel("老带新");
            info.setTwoChannel("老带新");
            info.setThrChannel("老带新");
        } else {
            id = "自到" + CommUtils.getUUID() + randomNumber;
        }
        info.setReservationsId(id);
        String infocustomer = "";
        String customerid = "";

        String sql = " select * from reservations_info where customer_phone='" + info.getCustomerPhone() + "'";
        List<CustomerInfo> customerInfo = sqlMapper.selectList(sql, CustomerInfo.class);

        if (customerInfo.size() > 0) {
            if (!"本人".equals(info.getPhoneSharShip())) {
                String ids = CommUtils.getUUID();
                /*//查询已有手机号的预约集合
                List<ReservationsInfo> existList = reservationsService.getCustomerPhoneCounts(info.getCustomerPhone() );
                int count = existList.size();
                String customerPhone = info.getCustomerPhone() + "+" + count;
                info.setCustomerPhone(customerPhone);*/
                String time= DateTimeKit.now();
                info.setFilingTime(time);
                infocustomer = service.insert2(info);  //前台新建客户
                String insql = "INSERT INTO `reservations_arching_info` (id,reservations_id, archiving,filing_time,one_channel,two_channel,thr_channel,update_time,update_people,remarks,follower,belonger) VALUES" +
                        "('" + ids + "','" + info.getReservationsId() + "','" + ShiroKit.getUser().getAccount() + "','" + time + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "'," +
                        "'" + time + "','" + ShiroKit.getUser().getAccount() + "','" + info.getRemarks() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "');";
                sqlMapper.insert(insql);
                model.addAttribute("customerId", infocustomer.split(",")[1]);
                customerid = infocustomer.split(",")[1];
            }else{
                return "已存在";
            }
        } else {
            if ("预约".equals(info.getCustomerStatus())) {
                String time= DateTimeKit.now();
                info.setFilingTime(time);
                infocustomer = service.insert2(info);  //前台新建客户
                model.addAttribute("customerId", infocustomer.split(",")[1]);
                customerid = infocustomer.split(",")[1];
            } else {
                String ids = CommUtils.getUUID();
                String time= DateTimeKit.now();
                String insql = "INSERT INTO `reservations_arching_info` (id,reservations_id, archiving,filing_time,one_channel,two_channel,thr_channel,update_time,update_people,remarks,follower,belonger) VALUES" +
                        "('" + ids + "','" + info.getReservationsId() + "','" + ShiroKit.getUser().getAccount() + "','" + time + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "'," +
                        "'" + time + "','" + ShiroKit.getUser().getAccount() + "','" + info.getRemarks() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "');";
                sqlMapper.insert(insql);
                reservationsService.insert(info);
                info.setDoctor("未指定医生");
                info.setStartTime(time);
                info.setFilingTime(time);
                service.insert(info);
            }
        }
        model.addAttribute("reservationsId", id);
        return "1" + "," + id + "," + customerid;
    }

    /**
     * 添加到店客户
     */
    @RequestMapping(value = "/getNatureCustomer",method = RequestMethod.POST)
    @ResponseBody
    public CustomerInfo getNatureCustomer(String customerId, Model model) throws Exception {
        String sql = " select customer_name as customerName,customer_age as customerAge," +
                "if(customer_sex='1','男','女') as sex,customer_phone as customerPhone from "+this.getTableName()+" where id='" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }


    @RequestMapping(value = "/addReconsuling")
    @ResponseBody
    public int addReconsuling(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select * from " + this.getTableName() + " a where a.customer_name='" + info.getCustomerName() + "'\n" +
                "and a.customer_phone='" + info.getCustomerPhone() + "' and \n" +
                "left(a.arrival_time,10)=left(curdate(),10) and customer_status!='作废'";
        List<CustomerInfo> list = sqlMapper.selectList(sql, CustomerInfo.class);
        if (list.size() > 0) {
            return 0;
        } else {
//            CustomerInfo cstomerInfo = service.selectOneById(info.getId());
//            info.setFirstVisitDate(cstomerInfo.getFirstVisitDate());
//            info.setMedicalRecordNumber(cstomerInfo.getMedicalRecordNumber());
            String sql2 = "select * from " + this.getTableName() + " where reservations_id= '" +  info.getReservationsId() + "' order by arrival_time desc limit 0,1";
            CustomerInfo cstomerInfo = sqlMapper.selectOne(sql2, CustomerInfo.class);
            if(cstomerInfo!=null){
                info.setFirstVisitDate(cstomerInfo.getFirstVisitDate());
                info.setMedicalRecordNumber(cstomerInfo.getMedicalRecordNumber());
            }
            service.reconsuling(info);
            return 1;
        }
    }

    /**
     * 删除预约客户
     */
    @RequestMapping(value = "/toVoid")
    @ResponseBody
    public int toVoid(@Valid CustomerInfo info, BindingResult result) throws Exception {
        return service.toVoid(info);
    }

    @RequestMapping(value = "/to_referee")
    public String to_archiving(Model model) {
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "referee.html";
    }

    @RequestMapping(value = "/getReferee", method = RequestMethod.POST)
    @ResponseBody
    public List<UserDto> getArchiving(String name) throws Exception {
        String sql = "select t.account from user t where t.account='" + name + "'" +
                " UNION select  t.customer_name as account from reservations_info t where t.customer_name='" + name + "'";
        List<UserDto> list = sqlMapper.selectList(sql, UserDto.class);
        return list;
    }

    @RequestMapping(value = "/to_addVisited/{reservationsId}")
    public String toAddVisited(@PathVariable String reservationsId, Model model) {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("reservationsId", reservationsId);
        return PREFIX + "addVisitedc.html";
    }


    @RequestMapping(value = "/to_revisitOperation/{reservationsId}")
    public String toRevisitOperation(@PathVariable String reservationsId, Model model) throws Exception {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select t2.id as customerId,t2.reservations_id as reservationsId,t2.customer_name as customerName,t2.medical_record_number as medicalRecordNumber" +
                ",t2.customer_sex as customerSex,t2.customer_age as customerAge ,t2.field_tc as tcName" +
                " from " + this.getTableName() + " t2 where reservations_id='" + reservationsId + "' order by t2.arrival_time desc limit 0,1";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql, ConsultantInfo.class);
        String sql1 = "select * from reservations_info where id='" + reservationsId + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql1, ReservationsInfo.class);
        List<AddVisitedInfo> revisitRecordList = consultantService.getRevisitRecordList(reservationsId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("revisitRecordList", revisitRecordList);
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("reservations", ShiroKit.getUser().getAccount());
        return PREFIX + "is_revisit.html";
    }

    /**
     * 修改到店客户
     */
    @RequestMapping(value = "/update")
    @ResponseBody
    public int update(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if ("直接上门".equals(info.getOneChannel())) {
            if (info.getReferee() != null && info.getReferee() != "") {
                info.setOneChannel("老带新");
                info.setTwoChannel("老带新");
                info.setThrChannel("老带新");
            }
        } else if ("老带新".equals(info.getOneChannel())) {
            if (info.getReferee() == null && info.getReferee() == "") {
                info.setOneChannel("直接上门");
                info.setTwoChannel("直接上门");
                info.setThrChannel("直接上门");
            }
        }
        info.setId(info.getIds());
        CustomerInfo customerInfo = service.selectOneById(info.getId());
//        if (!"本人".equals(info.getPhoneSharShip())) {
//            String sql = "update reservations_info a set a.id='" + info.getReservationsId() + info.getCustomerName() + "' " +
//                    "where a.id='" + info.getReservationsId() + "' and a.customer_name='" + info.getCustomerName() + "';";
//            sqlMapper.update(sql);
//            info.setReservationsId(info.getReservationsId() + info.getCustomerName());
//        } else {
        info.setReservationsId(customerInfo.getReservationsId());
//        }
        String selectsql = "select * from reservations_info where id='" + info.getReservationsId() + "' limit 0,1";
        ReservationsInfo reservations = sqlMapper.selectOne(selectsql, ReservationsInfo.class);
        if (reservations != null) {
            if (StringUtil.isEmpty(reservations.getTcName())) {
                String sql1 = "update reservations_info set tc_name='" + info.getFieldTc() + "' where id = '" + info.getReservationsId() + "'";
                sqlMapper.update(sql1);
            } else {
                if (customerInfo.getArrivalTime().substring(0, 10).equals(reservations.getFirstVisitDate())) {
                    String sql1 = "update reservations_info set tc_name='" + info.getFieldTc() + "' where id = '" + info.getReservationsId() + "'";
                    sqlMapper.update(sql1);
                }
            }
            String updateSql="update reservations_info set customer_sex=" + info.getCustomerSex() + ",customer_name='"+info.getCustomerName()+"',customer_age="+info.getCustomerAge()+"" +
                    ",identification_number='"+info.getMedicalRecordNumber()+"',one_medium='"+info.getOneMedium()+"',two_medium='"+info.getTwoMedium()+"',thr_medium='"+info.getThrMedium()+"'," +
                    "appointment_pay_service='"+info.getAppointmentPayService()+"',qq_number='"+info.getQqNumber()+"',wechat_number='"+info.getWechatNumber()+"',occupation='"+info.getOccupation()+"' where id = '" + info.getReservationsId() + "'";
            sqlMapper.update(updateSql);
        }
        service.update(info);
        return 1;
    }

    /**
     * 修改到店客户
     */
    @RequestMapping(value = "/add_isRevisit")
    @ResponseBody
    public int addIsRevisit(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String appointmentTime = "";
        if (info.getAppointmentTime() == null || info.getAppointmentTime() == "") {
            appointmentTime = "null";
        } else {
            appointmentTime = "'" + info.getAppointmentTime() + "'";
        }
        String sql = "update " + this.getTableName() + " set expenditure_project = '" + info.getExpenditureProject() + "', cause_of_no_return_visit='" + info.getCauseOfNoReturnVisit() + "',is_return_visit= 1,follow_status='" + info.getFollowStatus() + "',consumption_status='" + info.getConsumptionStatus() + "'," +
                "remarks_of_return_visit='" + info.getRemarksOfReturnVisit() + "',reservations='" + info.getReservations() + "',appointment_time=" + appointmentTime + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
                " where reservations_id='" + info.getReservationsId() + "'";
        consultantService.updateDiagnosisStatus(info.getId());
        String sql2 = "update `reservations_info` set expenditure_project = '" + info.getExpenditureProject() + "' where id ='" + info.getReservationsId() + "' ";
        sqlMapper.update(sql2);
        sqlMapper.update(sql);
        return consultantService.insert(info);
//        consultantService.updateIsReturnVisit(info);
//        if (info.getRevisitContent() == null || info.getRevisitContent() == "") {
//            String sql = "update " + this.getTableName() + " set cause_of_no_return_visit='" + info.getCauseOfNoReturnVisit() + "',is_return_visit=" + info.getIsReturnVisit() + ",follow_status='"+info.getFollowStatus()+"',consumption_status='"+info.getConsumptionStatus()+"'," +
//                    "remarks_of_return_visit='" + info.getRemarksOfReturnVisit() + "',reservations='" + info.getReservations() + "',appointment_time=" + appointmentTime + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
//                    " where reservations_id='" + info.getReservationsId() + "'";
//            if(info.getDiagnosisContent()!=null&&info.getDiagnosisContent()!=""){
//                consultantService.insert(info);
//            }
//            return sqlMapper.update(sql);
//        } else {
//            consultantService.insertReturnVisit(info);
//            String sqlselecthf = "select * from reservations_info a  where a.id='" + info.getReservationsId() + "' limit 0,1";
//            ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlselecthf, ReservationsInfo.class);
//            if (reservationsInfo != null) {
//                if (reservationsInfo.getHuifang() == null || reservationsInfo.getHuifang() == "") {
//                    String updatesql = "update reservations_info a set a.huifang=CONCAT(concat(char(10),',<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
//                    sqlMapper.update(updatesql);
//                } else {
//                    String updatesql = "update reservations_info a set a.huifang=CONCAT(huifang,concat(char(10),',<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
//                    sqlMapper.update(updatesql);
//                }
//            }
//            String sql = "update " + this.getTableName() + " set cause_of_no_return_visit='" + info.getCauseOfNoReturnVisit() + "',is_return_visit=" + info.getIsReturnVisit() + ",follow_status='"+info.getFollowStatus()+"',consumption_status='"+info.getConsumptionStatus()+"'," +
//                    "remarks_of_return_visit='" + info.getRemarksOfReturnVisit() + "',reservations='" + info.getReservations() + "',appointment_time=" + appointmentTime + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
//                    " where reservations_id='" + info.getReservationsId() + "'";
//            consultantService.updateDiagnosisStatus(info.getId());
//            if(info.getDiagnosisContent()!=null&&info.getDiagnosisContent()!=""){
//                consultantService.insert(info);
//            }
//            return sqlMapper.update(sql);
    }

    /**
     * TC 预约回访提交
     */
    @RequestMapping(value = "/add_yu_isRevisit")
    @ResponseBody
    public int addYuIsRevisit(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String appointmentTime = "";
        if (info.getAppointmentTime() == null || info.getAppointmentTime() == "") {
            appointmentTime = "null";
        } else {
            appointmentTime = "'" + info.getAppointmentTime() + "'";
        }
        String sql = "update " + this.getTableName() + " set cause_of_no_return_visit='" + info.getCauseOfNoReturnVisit() + "',is_return_visit=" + info.getIsReturnVisit() + ",follow_status='" + info.getFollowStatus() + "',consumption_status='" + info.getConsumptionStatus() + "'," +
                "remarks_of_return_visit='" + info.getRemarksOfReturnVisit() + "',reservations='" + info.getReservations() + "',appointment_time=" + appointmentTime + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
                " where reservations_id='" + info.getReservationsId() + "'";
        consultantService.insertReturnVisit(info);
        String sqlselecthf = "select id ,huifang as huifang from reservations_info a  where a.id='" + info.getReservationsId() + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlselecthf, ReservationsInfo.class);
        if (reservationsInfo != null) {
            if (reservationsInfo.getHuifang() == null || reservationsInfo.getHuifang() == "") {
                String updatesql = "update reservations_info a set a.huifang=CONCAT(concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
            } else {
                String updatesql = "update reservations_info a set a.huifang=CONCAT(huifang,concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
            }
        }
        return sqlMapper.update(sql);
    }


    /**
     * TC 预约回访提交
     */
    @RequestMapping(value = "/add_yu_isRevisit2")
    @ResponseBody
    public int addYuIsRevisit2(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String appointmentTime2 = "";
        if (info.getAppointmentTime2() == null || info.getAppointmentTime2() == "") {
            appointmentTime2 = "null";
        } else {
            appointmentTime2 = "'" + info.getAppointmentTime2() + "'";
        }
        String sql = "update " + this.getTableName() + " set cause_of_no_return_visit='" + info.getCauseOfNoReturnVisit() + "',is_return_visit=" + info.getIsReturnVisit() + ",follow_status='" + info.getFollowStatus2() + "',consumption_status='" + info.getConsumptionStatus2() + "'," +
                "remarks_of_return_visit='" + info.getRemarksOfReturnVisit() + "',reservations='" + info.getReservations() + "',appointment_time=" + appointmentTime2 + ",return_to_remark='" + ShiroKit.getUser().getAccount() + "'" +
                " where reservations_id='" + info.getReservationsId() + "'";
        consultantService.insertReturnVisit(info);
        String sqlselecthf = "select id ,huifang as huifang from reservations_info a  where a.id='" + info.getReservationsId() + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlselecthf, ReservationsInfo.class);
        if (reservationsInfo != null) {
            if (reservationsInfo.getHuifang() == null || reservationsInfo.getHuifang() == "") {
                String updatesql = "update reservations_info a set a.huifang=CONCAT(concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
            } else {
                String updatesql = "update reservations_info a set a.huifang=CONCAT(huifang,concat(char(10),' ,<b>" + DateTimeKit.now() + "</b>[" + ShiroKit.getUser().getAccount() + "]:" + info.getRevisitContent() + "'))  where id='" + info.getReservationsId() + "'";
                sqlMapper.update(updatesql);
            }
        }
        return sqlMapper.update(sql);
    }



    /**
     * 患者手机号查询信息
     */
    @RequestMapping(value = "/patientByPhone/{cri}")
    public String patientByPhone(@PathVariable String cri, Model model) throws Exception {
        String customerPhone = cri.split(",")[0];
        String customerName = cri.split(",")[1];

        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }

        String sql = "select t.quarters as address ,t.customer_sex as customerSex,t.* from reservations_info t " +
                "where customer_phone like '%" + customerPhone + "%' " +
                "order by filing_time desc limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql, ReservationsInfo.class);
        if (reservationsInfo != null) {
            if ("null".equals(reservationsInfo.getOccupation())) {
                reservationsInfo.setOccupation("");
            }
            model.addAttribute("customerInfo", reservationsInfo);
            model.addAttribute("customerPhone", reservationsInfo.getCustomerPhone());
            return PREFIX + "customer_updateInformation.html";
        } else {
            String sql2 = "select  (\n" +
                    "        CASE t.customer_sex\n" +
                    "        WHEN 1 THEN\n" +
                    "        '男'\n" +
                    "        WHEN 2 THEN\n" +
                    "        '女'\n" +
                    "        ELSE\n" +
                    "        '未知'\n" +
                    "        END\n" +
                    "        ) as sex  ,t.* from " + this.getTableName() + " t " +
                    "where  customer_phone like '%" + customerPhone + "%' and customer_name ='" + customerName + "'" +
                    " order by  create_appointment_time desc limit 0,1";
            CustomerInfo info = sqlMapper.selectOne(sql2, CustomerInfo.class);
            if (info != null) {
                model.addAttribute("customerInfo", info);
                model.addAttribute("customerPhone", info.getCustomerPhone());
                return PREFIX + "customer_updateInformation.html";
            } else {
                model.addAttribute("customerPhone", customerPhone);
                model.addAttribute("customerName", customerName);
                return PREFIX + "customer_addInformation.html";
            }
        }
    }

    //录入患者信息
    @RequestMapping(value = "/saveInformation", method = RequestMethod.POST)
    @ResponseBody
    public int saveInformation(String customerName, String customerPhone,
                               String wechatNumber, String occupation, String sex, String about,
                               String age, String regioni, String provincei, String citiesi,
                               String medicalReasons, String selectReasons, String address,
                               String phoneSharShip) throws Exception {
        String sql = "select * from medium where mediumname='" + selectReasons + "' and length(pids)>9   group by mediumname limit 0,1";
        Medium medium = sqlMapper.selectOne(sql, Medium.class);
        Integer pid = 110;
        if (medium != null) {
            pid = medium.getPid();
        }
        String sql2 = "select * from medium where id='" + pid + "' and length(pids)>5  group by mediumname limit 0,1";
        Medium medium2 = sqlMapper.selectOne(sql2, Medium.class);
        String mediumname2 = "直接上门";
        if (medium2 != null) {
            mediumname2 = medium2.getMediumname();
        }
        String sql3 = "select * from medium where id='" + medium2.getPid() + "' group by mediumname limit 0,1";
        Medium medium3 = sqlMapper.selectOne(sql3, Medium.class);
        String mediumname3 = "直接上门";
        if (medium3 != null) {
            mediumname3 = medium3.getMediumname();
        }
        String id = CommUtils.getUUID();
        String starttime = DateTimeKit.now();
        String time = starttime.substring(0, 11);
        int hour = Integer.parseInt(starttime.substring(11, 13));
        int minute = Integer.parseInt(starttime.substring(14, 16));
        minute = (minute / 15) * 15;
        String minutes = minute + "";
        if ("0".equals(minutes)) {
            minutes = "00";
        }
        starttime = time + hour + ":" + minutes + ":00";
        String endtime = "";
        if (hour + 1 == 24) {
            time = time.substring(0, 9) + (Integer.parseInt(time.substring(9, 10)) + 1) + " ";
            endtime = time + "00" + ":" + minutes + ":00";
        } else {
            endtime = time + (hour + 1) + ":" + minutes + ":00";
        }
        //预约号
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        String reservationsId = "自到" + CommUtils.getUUID() + randomNumber;
        if (wechatNumber == null || wechatNumber == "") {
            wechatNumber = "null";
        } else {
            wechatNumber = "'" + wechatNumber + "'";
        }
        String sql1 = " select max(yellow_number) as yellowNumber from " + this.getTableName() + " ";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        Integer yellowNumbers = 4397;
        if (customerInfo != null) {
            yellowNumbers = Integer.parseInt(customerInfo.getYellowNumber()) + 1;
        }
        //查询已有手机号的预约集合
        List<ReservationsInfo> existList = reservationsService.getCustomerPhoneCounts(customerPhone);
        int count = existList.size();
        if (!"本人".equals(phoneSharShip)) {
            customerPhone = customerPhone + "+" + count;
        }
        String sql21 = "select * from reservations_info where id='" + reservationsId + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql21, ReservationsInfo.class);
        String firstVisitDate = "";
        if (reservationsInfo != null) {
            if (reservationsInfo.getFirstVisitDate() == null || reservationsInfo.getFirstVisitDate() == "") {
                firstVisitDate = "'" + DateTimeKit.now() + "'";
            } else {
                firstVisitDate = "'" + reservationsInfo.getFirstVisitDate() + "'";
            }
        } else {
            firstVisitDate = "'" + DateTimeKit.now() + "'";
        }
        String sqladd = "INSERT INTO " + this.getTableName() + " (`id`,`reservations_id`,`first_visit_date`,`deal_status`, " +
                "`customer_name`,`one_medium`,`two_medium`,`thr_medium`,`one_channel`,`two_channel`,`thr_channel`," +
                " `customer_sex`, `customer_age`, `wechat_number`, `archiving`,`department`," +
                " `doctor`, `country`, `province`, `cities`, `region`,`occupation`," +
                "  `filing_time`,`customer_phone`,`start_time`,`end_time`,create_appointment_time,arrival_store," +
                "medical_reasons,select_reasons,yellow_number,address,phone_shar_ship) values " +
                "('" + id + "','" + reservationsId + "'," + firstVisitDate + ",'未开单','" + customerName + "'," +
                "'" + mediumname3 + "','" + mediumname2 + "','" + selectReasons + "','直接上门','直接上门','直接上门', " +
                " '" + sex + "', '" + age + "', " + wechatNumber + ",'" + ShiroKit.getUser().getAccount() + "','直接上门', " +
                " '未指定医生','中国','" + provincei + "', '" + citiesi + "','" + regioni + "','" + occupation + "' ," +
                " '" + DateTimeKit.now() + "'," + " '" + customerPhone + "','" + starttime + "','" + endtime
                + "','" + DateTimeKit.now() + "' ,'" + ShiroKit.getUser().getOutpatientName() + "','"
                + medicalReasons + "','" + selectReasons + "','" + yellowNumbers + "','" + address + "','" + phoneSharShip + "')";
        return sqlMapper.insert(sqladd);
    }

    //复诊修改患者信息
    @RequestMapping(value = "/updateInformation", method = RequestMethod.POST)
    @ResponseBody
    public int updateInformation(String customerName, String customerPhone,
                                 String wechatNumber, String occupation, String sex, String about,
                                 String age, String regioni, String provincei, String citiesi, String phoneSharShip,
                                 String medicalReasons, String selectReasons, String address) throws Exception {
        String sqlm1 = "select * from medium where mediumname='" + selectReasons + "' and length(pids)>9  group by mediumname limit 0,1";
        Medium medium = sqlMapper.selectOne(sqlm1, Medium.class);
        Integer pid = 110;
        if (medium != null) {
            pid = medium.getPid();
        }
        String sqlm2 = "select * from medium where id='" + pid + "'  and length(pids)>5  group by mediumname limit 0,1";
        Medium medium2 = sqlMapper.selectOne(sqlm2, Medium.class);
        String mediumname2 = "直接上门";
        if (medium2 != null) {
            mediumname2 = medium2.getMediumname();
        }
        String sqlm3 = "select * from medium where id='" + medium2.getPid() + "' group by mediumname limit 0,1";
        Medium medium3 = sqlMapper.selectOne(sqlm3, Medium.class);
        String mediumname3 = "直接上门";
        if (medium3 != null) {
            mediumname3 = medium3.getMediumname();
        }
        String sql1 = " select max(yellow_number) as yellowNumber from " + this.getTableName() + " ";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        Integer yellowNumbers = 4397;
        if (customerInfo != null) {
            yellowNumbers = Integer.parseInt(customerInfo.getYellowNumber()) + 1;
        }
        //查询已有手机号的预约集合
        List<ReservationsInfo> existList = reservationsService.getCustomerPhoneCounts(customerPhone);
        int count = existList.size();
        if (!"本人".equals(phoneSharShip)) {
            customerPhone = customerPhone + "+" + count;
            return this.saveInformation(customerName, customerPhone, wechatNumber, occupation,
                    sex, about, age, regioni, provincei, citiesi,
                    medicalReasons, selectReasons, address, phoneSharShip);
        } else {
            String sql = "select * from reservations_info where customer_phone='" + customerPhone + "' limit 0,1";
            ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql, ReservationsInfo.class);
            if (reservationsInfo != null) {
                String id = CommUtils.getUUID();
                String starttime = DateTimeKit.now();
                String time = starttime.substring(0, 11);
                int hour = Integer.parseInt(starttime.substring(11, 13));
                int minute = Integer.parseInt(starttime.substring(14, 16));
                minute = (minute / 15) * 15;
                String minutes = minute + "";
                if ("0".equals(minutes)) {
                    minutes = "00";
                }
                starttime = time + hour + ":" + minutes + ":00";
                String endtime = "";
                if (hour + 1 == 24) {
                    time = time.substring(0, 9) + (Integer.parseInt(time.substring(9, 10)) + 1) + " ";
                    endtime = time + "00" + ":" + minutes + ":00";
                } else {
                    endtime = time + (hour + 1) + ":" + minutes + ":00";
                }
                String sql3 = "select * from " + this.getTableName() + " where customer_phone='" + customerPhone + "' and " +
                        "left(create_appointment_time,10) ='" + starttime.substring(0, 10) + "'";
                List<CustomerInfo> cinfo2 = sqlMapper.selectList(sql3, CustomerInfo.class);
                if (cinfo2.size() > 0) {
                    String sqlupd = "update " + this.getTableName() + "  set  " +
                            "start_time='" + starttime + "',end_time='" + endtime + "'," +
                            "archiving='" + ShiroKit.getUser().getAccount() + "'," +
                            "create_appointment_time = '" + DateTimeKit.now() + "'," +
                            "customer_name= '" + customerName + "'," +
                            "customer_age= '" + age + "'," +
                            "customer_sex= '" + sex + "'," +
                            "occupation= '" + occupation + "'," +
                            "address= '" + address + "'," +
                            "province= '" + provincei + "'," +
                            "cities= '" + citiesi + "'," +
                            "region= '" + regioni + "'," +
                            "medical_reasons= '" + medicalReasons + "'," +
                            "one_medium= '" + mediumname3 + "'," +
                            "two_medium= '" + mediumname2 + "'," +
                            "thr_medium= '" + selectReasons + "' " +
                            " where customer_phone='" + customerPhone + "'" +
                            " and left(start_time,10)='" + starttime.substring(0, 10) + "'";
                    return sqlMapper.update(sqlupd);
                } else {
                    String sql11 = "INSERT INTO " + this.getTableName() + " (`id`,`first_visit_date`," +
                            "`age_range`,`reservations_id`," +
                            "`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`,  " +
                            "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                            " `customer_sex`, `customer_age`,  " +
                            " `qq_number`, `wechat_number`, `other_contact`, `doctor`,`country`," +
                            " `province`, `cities`, `region`,`address`," +
                            " `archiving`, `entry_clerk` ,`update_people`," +
                            "  `filing_time`," +
                            " `customer_phone`,`remarks`,`start_time`,`end_time`,create_appointment_time,occupation,yellow_number)  " +
                            " select '" + id + "',first_visit_date," +
                            "  age_range,id," +
                            " '" + ShiroKit.getUser().getOutpatientName() + "',phone_shar_ship,'未开单','" + customerName + "' ," +
                            "'" + mediumname3 + "','" + mediumname2 + "','" + selectReasons + "', one_channel,two_channel,thr_channel," +
                            " '" + sex + "', '" + age + "', " +
                            " qq_number,wechat_number,other_contact,'未指定医生',country," +
                            " '" + provincei + "', '" + citiesi + "','" + regioni + "','" + address + "'," +
                            " '" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "', '" + DateTimeKit.now() + "'," +
                            " '" + customerPhone + "'," +
                            " remarks ,'" + starttime + "','" + endtime + "','" + DateTimeKit.now() + "','" + occupation + "','" + yellowNumbers + "'" +
                            " from reservations_info" +
                            " where customer_phone='" + customerPhone + "' limit 0,1";
                    return sqlMapper.insert(sql11);
                }
            } else {
                String sql2 = "select * from " + this.getTableName() + " where customer_phone='" + customerPhone + "'  limit 0,1";
                CustomerInfo info = sqlMapper.selectOne(sql2, CustomerInfo.class);
                if (info != null) {
                    String id = CommUtils.getUUID();
                    String starttime = DateTimeKit.now();
                    String time = starttime.substring(0, 11);
                    int hour = Integer.parseInt(starttime.substring(11, 13));
                    int minute = Integer.parseInt(starttime.substring(14, 16));
                    minute = (minute / 15) * 15;
                    String minutes = minute + "";
                    if ("0".equals(minutes)) {
                        minutes = "00";
                    }
                    starttime = time + hour + ":" + minutes + ":00";
                    String endtime = "";
                    if (hour + 1 == 24) {
                        time = time.substring(0, 9) + (Integer.parseInt(time.substring(9, 10)) + 1) + " ";
                        endtime = time + "00" + ":" + minutes + ":00";
                    } else {
                        endtime = time + (hour + 1) + ":" + minutes + ":00";
                    }
                    String sql3 = "select * from " + this.getTableName() + " where customer_phone='" + customerPhone + "' and " +
                            "left(create_appointment_time,10) ='" + starttime.substring(0, 10) + "'";
                    List<CustomerInfo> cinfo2 = sqlMapper.selectList(sql3, CustomerInfo.class);
                    if (cinfo2.size() > 0) {
                        String sqlupd2 = "update " + this.getTableName() + "  set  " +
                                "start_time='" + starttime + "',end_time='" + endtime + "'," +
                                "archiving='" + ShiroKit.getUser().getAccount() + "'," +
                                "create_appointment_time = '" + DateTimeKit.now() + "'," +
                                "customer_name= '" + customerName + "'," +
                                "customer_age= '" + age + "'," +
                                "customer_sex= '" + sex + "'," +
                                "occupation= '" + occupation + "'," +
                                "address= '" + address + "'," +
                                "province= '" + provincei + "'," +
                                "cities= '" + citiesi + "'," +
                                "region= '" + regioni + "'," +
                                "medical_reasons= '" + medicalReasons + "'," +
                                "one_medium= '" + mediumname3 + "'," +
                                "two_medium= '" + mediumname2 + "'," +
                                "thr_medium= '" + selectReasons + "' " +
                                " where customer_phone='" + customerPhone + "'" +
                                " and left(start_time,10)='" + starttime.substring(0, 10) + "'";
                        return sqlMapper.update(sqlupd2);
                    } else {
                        String sql22 = "INSERT INTO " + this.getTableName() + " (`id`,`first_visit_date`,`actual_account_balance`," +
                                "`give_account_balance`,`account_arrears`,`age_range`,`reservations_id`," +
                                "`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, `pseudonym`,   " +
                                "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                                " `customer_sex`, `customer_age`, `birthday`,  `address`, `referee`, `referee_ship`, `id_card_no`," +
                                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`, " +
                                "  `number_of_audits`, `auditor_time`, `final_auditor`," +
                                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                                " `qq_number`, `wechat_number`, `other_contact`,`doctor`, `country`," +
                                " `province`, `cities`, `region`, `medical_record_number`, " +
                                " `marital_status`, `occupation`, `archiving`, `entry_clerk` ,`update_people`," +
                                "  `filing_time`,`customer_phone`,`remarks`,`start_time`,`end_time`," +
                                "create_appointment_time,medical_reasons,yellow_number)  " +
                                " select '" + id + "',first_visit_date,actual_account_balance," +
                                " give_account_balance,account_arrears, age_range,reservations_id," +
                                " arrival_store,phone_shar_ship,'未开单','" + customerName + "' , pseudonym,  " +
                                "'" + mediumname3 + "','" + mediumname2 + "','" + selectReasons + "', one_channel,two_channel,thr_channel," +
                                " '" + sex + "', '" + age + "', birthday, '" + address + "', referee, referee_ship, id_card_no," +
                                " fixed_telephone, is_have_children, email, zip_code," +
                                "   number_of_audits, auditor_time, final_auditor," +
                                " non_transaction_level,transaction_level,characters," +
                                " consumption_habits,is_return_visit,cause_of_no_return_visit,consum_ability," +
                                " qq_number,wechat_number,other_contact,'未指定医生',country," +
                                " '" + provincei + "', '" + citiesi + "','" + regioni + "',medical_record_number,marital_status," +
                                " '" + occupation + "',archiving,entry_clerk,update_people, '" + DateTimeKit.now() + "'," +
                                " '" + customerPhone + "',remarks ,'" + starttime + "','" + endtime + "','" +
                                DateTimeKit.now() + "','" + medicalReasons + "','" + yellowNumbers + "' from " + this.getTableName()
                                + " where customer_phone='" + customerPhone + "' limit 0,1";
                        return sqlMapper.insert(sql22);
                    }
                } else {
                    return 1;
                }
            }
        }
    }


    /**
     * 跳转医生个人预约
     */
    @RequestMapping("/customer_information")
    public String customer_information(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "/customer_information.html";
    }


    /**
     * 跳转返回预约视图
     */
    @RequestMapping("/customer_reservationViews")
    public String customer_reservationViews(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "/customer_reservationViews.html";
    }

    /**
     * 跳转信息录入
     */
    @RequestMapping("/customer_doctorWeek/{id}")
    public String customer_doctorWeek(@PathVariable String id, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from user where id ='" + id + "'";
        User user = sqlMapper.selectOne(sql, User.class);
        model.addAttribute("id", id);
        model.addAttribute("doctorName", user.getAccount());
        return PREFIX + "/customer_doctorWeek.html";
    }

/*
    */

    /**
     * 跳转用户填写信息
     *//*
    @RequestMapping(value = "/customer_addInformation/{customerPhone}")
    public String customer_addInformation(@PathVariable String customerPhone, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("customerPhone", customerPhone);
        return PREFIX + "/customer_addInformation.html";
    }*/
    @RequestMapping(value = "/to_reDiagnosis", method = RequestMethod.POST)
    @ResponseBody
    public int to_reDiagnosis(String customerId, HttpServletRequest request) {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        CustomerInfo info = service.selectOneById(customerId);
        String sql = "select * from " + this.getTableName() + " a where a.customer_name='" + info.getCustomerName() + "'\n" +
                "and a.customer_phone='" + info.getCustomerPhone() + "' and \n" +
                "left(a.arrival_time,10)=left(curdate(),10) and customer_status!='作废'";
        List<CustomerInfo> list = sqlMapper.selectList(sql, CustomerInfo.class);
        if (list.size() > 0) {
            return 0;
        } else {
            CustomerInfo customerInfo = this.service.selectOneById(customerId);
            customerInfo.setCustomerStatus("复诊");
            service.reconsuling(customerInfo);
            return 1;
        }
    }

    public String getTableName() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return tableName;
    }

    @RequestMapping(value = "/customer_import")
    public String to_import(Model model) {
        return PREFIX + "customer_import.html";
    }


    /**
     * 查询客户信息
     */

    @RequestMapping(value = "/getCustomerByReach", method = RequestMethod.POST)
    @ResponseBody
    public List<CustomerInfo> getCustomerByReach(String reach,String pageStart,String pageSize, Model model) {
        String sql2 = " ";
        if (StringUtils.isNotBlank(reach)) {
            sql2 = "  and (t.customer_name like CONCAT('%" + reach + "%') or t.customer_phone like CONCAT('%" + reach + "%') or " +
                    "  t.wechat_number like CONCAT('%" + reach + "%') ) ";
        }

        String sql = "select * from (select if(t.customer_sex='1','男','女') as sex, t.id,t.customer_name as customerName,t.customer_age as customerAge," +
                "t.customer_phone as customerPhone,t.arrival_store as arrivalStore  from "+this.getTableName()+" t " +
                " where 1=1 " + sql2+" order by arrival_time desc ) t2 group by t2.customerPhone" +
                " limit 0,"+Integer.parseInt(pageStart)*Integer.parseInt(pageSize)+"";
        List<CustomerInfo> customerInfo = sqlMapper.selectList(sql, CustomerInfo.class);
        return customerInfo;
    }


    /**
     * 查询客户信息
     */

    @RequestMapping(value = "/getCustomerByStore", method = RequestMethod.POST)
    @ResponseBody
    public List<CustomerInfo> getCustomerByStore(String reach,String pageStart,String pageSize, Model model) {
        String sql2 = " ";
        if (StringUtils.isNotBlank(reach)) {
            sql2 = "  and ( t.customer_name like CONCAT('%" + reach + "%') or  t.customer_phone like CONCAT('%" + reach + "%') or " +
                    "  t.wechat_number like CONCAT('%" + reach + "%') ) ";
        }
        String sql = "select id,customerName, customerAge,customerPhone,reservationsId, sex,arrivalStore from (select t.id as id,t.customer_name as customerName,t.customer_age as customerAge," +
                "t.customer_phone as customerPhone, t.reservations_id as reservationsId,   (\n" +
                "        CASE t.customer_sex\n" +
                "        WHEN 1 THEN\n" +
                "            '男'\n" +
                "        WHEN 2 THEN\n" +
                "            '女'\n" +
                "        ELSE\n" +
                "            '未知'\n" +
                "        END\n" +
                "    ) as sex,t.arrival_store as arrivalStore   from " + this.getTableName() + " t " +
                "where 1=1 " + sql2 + " and t.arrival_status!=1) t1  group by reservationsId " +
                "limit 0,"+Integer.parseInt(pageStart)*Integer.parseInt(pageSize);
        List<CustomerInfo> customerInfo = sqlMapper.selectList(sql, CustomerInfo.class);
        return customerInfo;
    }

    /**
     * 查询客户信息
     */
    @RequestMapping(value = "/getCustomerByReservationsId/{customerid}", method = RequestMethod.POST)
    @ResponseBody
    public List<CustomerInfo> getCustomerByReservationsId(@PathVariable String customerid, Model model) {
        if (ToolUtil.isEmpty(customerid)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select * from "+this.getTableName()+" t" +
                " where t.id='" + customerid + "' limit 0,1";
        List<CustomerInfo> customerInfo = sqlMapper.selectList(sql, CustomerInfo.class);
        return customerInfo;
    }

 /*   *//**
     * 前台保存预约
     *//*
    @RequestMapping(value = "/SaveReservation", method = RequestMethod.POST)
    @ResponseBody
    public int SaveReservation(String customerPhone, String projectData,String customerStatus,
                               String fieldTc,String doctor, String remarks,String reservationsId,
                               String startTimeValue, String endTimeValue,String customerId) throws Exception {
        //改变详情表状态
        String sql = "update "+ this.getTableName() +"  set appointment_pay_service= '"+projectData+"'," +
                " field_tc='"+fieldTc+"',customer_status='"+customerStatus+"',doctor='"+doctor+"',remarks='"+remarks
                +"',start_time='"+startTimeValue+"',end_time='"+endTimeValue+"',archiving='"+ShiroKit.getUser().getAccount()+"',create_appointment_time = '"+ DateTimeKit.now()+"'" +
                " where reservations_id='"+reservationsId+"'" +
                " and id='"+customerId+"'";
        return sqlMapper.update(sql);
    }*/

    /**
     * 前台修改保存预约
     */
    @RequestMapping(value = "/updateReservation", method = RequestMethod.POST)
    @ResponseBody
    public int updateReservation(String customerPhone, String projectData, String duration,
                                 String fieldTc, String doctor, String remarks, String reservationsId,
                                 String startTimeValue, String customerId) throws Exception {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = formatter.parse(startTimeValue);
        Date date1 = new Date(date.getTime() + Integer.parseInt(duration) * 60 * 1000);
        String endTimeValue = formatter.format(date1);

        //改变详情表状态
        String sql = "update " + this.getTableName() + "  set appointment_pay_service= '" + projectData + "'," +
                " field_tc='" + fieldTc + "',doctor='" + doctor + "',remarks='" + remarks
                + "',start_time='" + startTimeValue + "',end_time='" + endTimeValue + "',archiving='" + ShiroKit.getUser().getAccount() + "',create_appointment_time = '" + DateTimeKit.now() + "'" +
                " where id='" + customerId + "'";

        String sql2 = "select t.customer_name as customerName,t.customer_phone as customerPhone,t.field_tc as fieldTc,t.start_time as startTime,t.doctor as doctor,t.appointment_pay_service as appointmentPayService from " + this.getTableName() + " t where id='" + customerId + "'";
        CustomerInfo info = sqlMapper.selectOne(sql2, CustomerInfo.class);

        String sq11 = "INSERT INTO `customer_reservations_update_log` (`logtype`, `userid`, `customer_name`,`customer_phone`,`single_person`, `createtime`,`succeed`, `message`,`classname`,`remarks`,appointment_pay_service,field_tc,time,doctor) VALUES " +
                " ( '修改'," + ShiroKit.getUser().getId() + ", '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'成功','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + " 把 " + info.getCustomerName() + " 的预约时间:" + info.getStartTime() + " 修改为:" + startTimeValue + "  预约项目:" + info.getAppointmentPayService() + " 修改为:" + projectData + "','" + remarks + "', '" + projectData + "','" + fieldTc + "','" + startTimeValue + "','" + doctor + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    /**
     * 通过 ID 查看预约患者信息
     */
    @RequestMapping(value = "/getCustomerById/{id}", method = RequestMethod.POST)
    @ResponseBody
    public CustomerInfo getCustomerById(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select * from " + this.getTableName() + " t" +
                " where t.customer_status!='作废' and t.id='" + id + "' ";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    /**
     * 复诊新加一条用户数据
     */
    @RequestMapping(value = "/addCustomerFz", method = RequestMethod.POST)
    @ResponseBody
    public int addCustomerFz(String customerPhone, String projectData, String duration,
                             String fieldTc, String doctor, String remarks, String reservationsId,
                             String startTimeValue, String customerId) throws Exception {

        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = formatter.parse(startTimeValue);
        Date date1 = new Date(date.getTime() + Integer.parseInt(duration) * 60 * 1000);
        String endTimeValue = formatter.format(date1);

        String id = CommUtils.getUUID();
        String tableHead = ShiroKit.getUser().getAbbreviation();
        id = id + tableHead;
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        /*String sql1 = "select * from " + this.getTableName() + " where reservations_id='" + reservationsId + "' and " +
                "left(start_time,10)='" + startTimeValue.substring(0, 10) + "'";
        List<CustomerInfo> cinfo = sqlMapper.selectList(sql1, CustomerInfo.class);*/
        String sql11 = " select max(yellow_number) as yellowNumber from " + this.getTableName() + " ";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql11, CustomerInfo.class);
        if ("洁牙,".equals(projectData)) {
            projectData = "洁牙";
        }
        Integer yellowNumbers = 4397;
        if (customerInfo != null) {
            yellowNumbers = Integer.parseInt(customerInfo.getYellowNumber()) + 1;
        }
        /*if (cinfo.size() > 0) {
            String sql = "update " + this.getTableName() + "  set appointment_pay_service= '" + projectData + "'," +
                    " field_tc='" + fieldTc + "',doctor='" + doctor + "',remarks='" + remarks
                    + "',start_time='" + startTimeValue + "',end_time='" + endTimeValue + "',archiving='" + ShiroKit.getUser().getAccount() + "',create_appointment_time = '" + DateTimeKit.now() + "'" +
                    " where reservations_id='" + reservationsId + "'" +
                    " and left(start_time,10)='" + startTimeValue.substring(0, 10) + "'";
            return sqlMapper.update(sql);
        } else {
            String sql2 = "select * from " + this.getTableName() + " where customer_phone='" + customerPhone + "' and " +
                    "left(start_time,10)='" + startTimeValue.substring(0, 10) + "'";
            List<CustomerInfo> cinfo1 = sqlMapper.selectList(sql2, CustomerInfo.class);
            if (cinfo1.size() > 0) {
                String sql = "update " + this.getTableName() + "  set appointment_pay_service= '" + projectData + "'," +
                        " field_tc='" + fieldTc + "',doctor='" + doctor + "',remarks='" + remarks
                        + "',start_time='" + startTimeValue + "',end_time='" + endTimeValue + "',archiving='" + ShiroKit.getUser().getAccount() + "',create_appointment_time = '" + DateTimeKit.now() + "'" +
                        " where customer_phone='" + customerPhone + "'" +
                        " and left(start_time,10)='" + startTimeValue.substring(0, 10) + "'";
                return sqlMapper.update(sql);
            } else {
                String sql3 = "select * from " + this.getTableName() + " where customer_phone='" + customerPhone + "' and " +
                        "filing_time='" + startTimeValue.substring(0, 10) + "'";
                List<CustomerInfo> cinfo2 = sqlMapper.selectList(sql3, CustomerInfo.class);
                if (cinfo2.size() > 0) {
                    String sql = "update " + this.getTableName() + "  set appointment_pay_service= '" + projectData + "'," +
                            " field_tc='" + fieldTc + "',doctor='" + doctor + "',yellow_number='" + yellowNumbers + "',remarks='" + remarks
                            + "',start_time='" + startTimeValue + "',end_time='" + endTimeValue + "',archiving='" + ShiroKit.getUser().getAccount() + "',create_appointment_time = '" + DateTimeKit.now() + "'" +
                            " where customer_phone='" + customerPhone + "'" +
                            " and filing_time='" + startTimeValue.substring(0, 10) + "'";
                    return sqlMapper.update(sql);
                } else {*/
        String sql4 = "select * from " + this.getTableName() + " where reservations_id='" + reservationsId + "' limit 0,1";
        List<CustomerInfo> cinfo4 = sqlMapper.selectList(sql4, CustomerInfo.class);
        if (cinfo4.size() > 0) {
            String sql = "INSERT INTO " + this.getTableName() + " (`id`,`first_visit_date`,`actual_account_balance`," +
                    "`give_account_balance`,`account_arrears`,`age_range`,`reservations_id`,`appointment_pay_service`," +
                    "`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, `pseudonym`,   " +
                    "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                    " `customer_sex`, `customer_age`, `birthday`,  `address`, `referee`, `referee_ship`, `id_card_no`," +
                    " `fixed_telephone`,`diagnosis_status`, `is_have_children`, `email`, `zip_code`, " +
                    "  `number_of_audits`, `auditor_time`, `final_auditor`," +
                    "  `non_transaction_level`, `transaction_level`, `characters`, " +
                    "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                    " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                    " `province`, `cities`, `region`, `medical_record_number`, " +
                    " `marital_status`, `occupation`, `archiving`, `entry_clerk` ,`update_people`," +
                    "  `filing_time`," +
                    " `customer_phone`,`field_tc`,`doctor`,`remarks`,`start_time`,`end_time`,create_appointment_time,yellow_number)  " +
                    " select '" + id + "',first_visit_date,actual_account_balance," +
                    " give_account_balance,account_arrears, age_range,reservations_id,'" + projectData + "'," +
                    " arrival_store,phone_shar_ship,'未成交',customer_name , pseudonym,  " +
                    "one_medium,two_medium,thr_medium, one_channel,two_channel,thr_channel," +
                    " customer_sex, customer_age, birthday, address, referee, referee_ship, id_card_no," +
                    " fixed_telephone,'未看诊', is_have_children, email, zip_code," +
                    "   number_of_audits, auditor_time, final_auditor," +
                    " non_transaction_level,transaction_level,characters," +
                    " consumption_habits,is_return_visit,cause_of_no_return_visit,consum_ability," +
                    " qq_number,wechat_number,other_contact,country," +
                    " province, cities,region,medical_record_number,marital_status," +
                    " occupation,archiving,entry_clerk,update_people, '" + DateTimeKit.now() + "'," +
                    " '" + customerPhone + "','" + fieldTc + "','" + doctor + "'," +
                    "'" + remarks + "','" + startTimeValue + "','" + endTimeValue + "','" + DateTimeKit.now() + "','" + yellowNumbers + "' from " + this.getTableName()
                    + " where reservations_id='" + reservationsId + "' limit 0,1";
            int a= sqlMapper.insert(sql);
            String sql2="delete from "+ this.getTableName() +" where reservations_id='" + reservationsId +"' and xcustomer_status='预约' and left(filing_time,10)=left(now(),10) ";
            sqlMapper.delete(sql2);
            return  a;
        } else {
            String sql = "INSERT INTO " + this.getTableName() + " (`id`,`diagnosis_status`,`field_tc`,`doctor`,`first_visit_date`," +
                    "`age_range`,`reservations_id`,`appointment_pay_service`," +
                    "`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`,  " +
                    "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                    " `customer_sex`, `customer_age`,  " +
                    " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                    " `province`, `cities`, `region`,`address`," +
                    " `archiving`, `entry_clerk` ,`update_people`," +
                    "  `filing_time`," +
                    " `customer_phone`,`remarks`,`start_time`,`end_time`,create_appointment_time,yellow_number)  " +
                    " select '" + id + "','未看诊','" + fieldTc + "','" + doctor + "',first_visit_date," +
                    "  age_range,id,'" + projectData + "'," +
                    " '" + ShiroKit.getUser().getOutpatientName() + "',phone_shar_ship,'未成交',customer_name ," +
                    "one_medium,two_medium,thr_medium, one_channel,two_channel,thr_channel," +
                    " customer_sex, customer_age, " +
                    " qq_number,wechat_number,other_contact,country,quarters," +
                    " province, cities,region," +
                    " '" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "', '" + DateTimeKit.now() + "'," +
                    " '" + customerPhone + "'," +
                    "'" + remarks + "','" + startTimeValue + "','" + endTimeValue + "','" + DateTimeKit.now() + "','" + yellowNumbers + "' from reservations_info" +
                    " where id='" + reservationsId + "' limit 0,1";
            return sqlMapper.insert(sql);
        }
          /*      }
            }
         }*/
    }

    /**
     * 复诊新加一条用户数据  在预约  医生预约
     */
    @RequestMapping(value = "/addCustomerFzs", method = RequestMethod.POST)
    @ResponseBody
    public int addCustomerFzs(String customerPhone, String projectData,
                              String fieldTc, String doctor, String remarks, String reservationsId,
                              String startTimeValue, String endTimeValue, String customerId) throws Exception {
        String id = CommUtils.getUUID();
        String tableHead = ShiroKit.getUser().getAbbreviation();
        id = id + tableHead;
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        String sql11 = " select max(yellow_number) as yellowNumber from " + this.getTableName() + " ";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql11, CustomerInfo.class);
        Integer yellowNumbers = 4397;
        if (startTimeValue.compareTo((DateTimeKit.now())) < 0) {
            return 11;
        } else {
        /*    String sql1 = "select * from " + this.getTableName() + " where reservations_id='" + reservationsId + "' and " +
                    "left(start_time,10)='" + startTimeValue.substring(0, 10) + "'";
            List<CustomerInfo> cinfo = sqlMapper.selectList(sql1, CustomerInfo.class);
            if (cinfo.size() > 0) {
                String sql = "update " + this.getTableName() + "  set appointment_pay_service= '" + projectData + "'," +
                        " field_tc='" + fieldTc + "',doctor='" + doctor + "',remarks='" + remarks
                        + "',start_time='" + startTimeValue + "',end_time='" + endTimeValue + "',archiving='" + ShiroKit.getUser().getAccount() + "',create_appointment_time = '" + DateTimeKit.now() + "'" +
                        " where reservations_id='" + reservationsId + "'" +
                        " and left(start_time,10)='" + startTimeValue.substring(0, 10) + "'";
                return sqlMapper.update(sql);
            } else {*/
            String sql = "INSERT INTO " + this.getTableName() + " (`id`,`diagnosis_status`,`first_visit_date`,`actual_account_balance`," +
                    "`give_account_balance`,`account_arrears`,`age_range`,`reservations_id`,`appointment_pay_service`," +
                    "`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, `pseudonym`,   " +
                    "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                    " `customer_sex`, `customer_age`, `birthday`,  `address`, `referee`, `referee_ship`, `id_card_no`," +
                    " `fixed_telephone`, `is_have_children`, `email`, `zip_code`, " +
                    "  `number_of_audits`, `auditor_time`, `final_auditor`," +
                    "  `non_transaction_level`, `transaction_level`, `characters`, " +
                    "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                    " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                    " `province`, `cities`, `region`, `medical_record_number`, " +
                    " `marital_status`, `occupation`, `archiving`, `entry_clerk` ,`update_people`," +
                    "  `filing_time`," +
                    " `customer_phone`,`field_tc`,`doctor`,`remarks`,`start_time`,`end_time`,create_appointment_time,yellow_number)  " +
                    " select '" + id + "','未看诊',first_visit_date,actual_account_balance," +
                    " give_account_balance,account_arrears, age_range,reservations_id,'" + projectData + "'," +
                    " arrival_store,phone_shar_ship,'未成交',customer_name , pseudonym,  " +
                    "one_medium,two_medium,thr_medium, one_channel,two_channel,thr_channel," +
                    " customer_sex, customer_age, birthday, address, referee, referee_ship, id_card_no," +
                    " fixed_telephone, is_have_children, email, zip_code," +
                    "   number_of_audits, auditor_time, final_auditor," +
                    " non_transaction_level,transaction_level,characters," +
                    " consumption_habits,is_return_visit,cause_of_no_return_visit,consum_ability," +
                    " qq_number,wechat_number,other_contact,country," +
                    " province, cities,region,medical_record_number,marital_status," +
                    " occupation,archiving,entry_clerk,update_people, '" + DateTimeKit.now() + "'," +
                    " '" + customerPhone + "','" + fieldTc + "','" + doctor + "'," +
                    "'" + remarks + "','" + startTimeValue + "','" + endTimeValue + "','" + DateTimeKit.now() + "','" + yellowNumbers + "' from " + this.getTableName()
                    + " where id='" + customerId + "' and reservations_id='" + reservationsId + "' ";
            return sqlMapper.insert(sql);
//            }
        }
    }

    /**
     * 就诊记录 修改 保存
     */
    @RequestMapping(value = "/updateCustomerFzs", method = RequestMethod.POST)
    @ResponseBody
    public int updateCustomerFzs(String customerPhone, String projectData,
                                 String fieldTc, String doctor, String remarks, String reservationsId,
                                 String startTimeValue, String endTimeValue, String customerId) throws Exception {

        String sql = "update " + this.getTableName() + "  set appointment_pay_service= '" + projectData + "'," +
                " field_tc='" + fieldTc + "',doctor='" + doctor + "',remarks='" + remarks + "',start_time='" + startTimeValue + "'," +
                "end_time='" + endTimeValue + "',archiving='" + ShiroKit.getUser().getAccount() + "',create_appointment_time = '" + DateTimeKit.now() + "'" +
                " where id='" + customerId + "'";
        return sqlMapper.update(sql);
    }


    /**
     * 导入
     */
    @RequestMapping(value = "/upload")
    public String importTestQuestions(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    Cell xh9 = hssfRow.getCell(8);
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);
                    Cell xh14 = hssfRow.getCell(13);
                    Cell xh15 = hssfRow.getCell(14);
                    Cell xh16 = hssfRow.getCell(15);
                    Cell xh17 = hssfRow.getCell(16);
                    Cell xh18 = hssfRow.getCell(17);
                    Cell xh19 = hssfRow.getCell(18);
                    Cell xh20 = hssfRow.getCell(19);
                    Cell xh21 = hssfRow.getCell(20);
                    Cell xh22 = hssfRow.getCell(21);
                    Cell xh23 = hssfRow.getCell(22);
                    Cell xh24 = hssfRow.getCell(23);
                    Cell xh25 = hssfRow.getCell(24);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    String xh14s = "";
                    String xh15s = "";
                    String xh16s = "";
                    String xh17s = "";
                    String xh18s = "";
                    String xh19s = "";
                    String xh20s = "";
                    String xh21s = "";
                    String xh22s = "";
                    String xh23s = "";
                    String xh24s = "";
                    String xh25s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3) == null || ("").equals(xh3)) {
                        xh3s = "null";
                    } else {
                        if ("男".equals((xh3 + ""))) {
                            xh3s = "'" + 1 + "'";
                        } else if ("女".equals((xh3 + ""))) {
                            xh3s = "'" + 2 + "'";
                        } else if ("未知".equals((xh3 + ""))) {
                            xh3s = "null";
                        }
                    }
                    if ((xh4) == null || ("").equals(xh4)) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if (xh5.toString() != null) {
                        xh5s = xh5.toString();
                    } else {
                        xh5s = null;
                    }
//                    Date d = xh6.getDateCellValue();
//                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                    String str2  = formater.format(d);
//                    System.out.println(str2);

//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                    String str = sdf.format(HSSFDateUtil.getJavaDate(xh6.getNumericCellValue())).toString();
//                    System.out.println(str);
                    if ((xh6 + "") == null || ("").equals((xh6 + ""))) {
                        xh6s = "null";
                    } else {
                        xh6s = "" + xh6 + "";
                    }
                    if ((xh7 + "") == null || ("").equals((xh7 + ""))) {
                        xh7s = "null";
                    } else {
                        xh7s = "'" + xh7 + "'";
                    }
                    if ((xh8 + "") == null || ("").equals((xh8 + ""))) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if ((xh9 + "") == null || ("").equals((xh9 + ""))) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11) == null || ("").equals(xh11)) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12 + "") == null || ("").equals((xh12 + ""))) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13 + "") == null || ("").equals((xh13 + ""))) {
                        xh13s = "null";
                    } else {
                        xh13s = "" + xh13 + "";
                    }
                    if ((xh14) == null || ("").equals((xh14))) {
                        xh14s = "null";
                    } else {
                        xh14s = "'" + xh14 + "'";
                    }
                    if ((xh15 + "") == null || ("").equals((xh15 + ""))) {
                        xh15s = "null";
                    } else {
                        xh15s = "'" + xh15 + "'";
                    }
                    if ((xh16) == null || ("").equals(xh16)) {
                        xh16s = "null";
                    } else {
                        if ("是".equals((xh16 + ""))) {
                            xh16s = "'已成交'";
                        } else if ("否".equals((xh16 + ""))) {
                            xh16s = "'未成交'";
                        }
                    }
                    if ((xh17 + "") == null || ("").equals((xh17 + ""))) {
                        xh17s = "null";
                    } else {
                        xh17s = "'" + xh17 + "'";
                    }
                    if ((xh18 + "") == null || ("").equals((xh18 + ""))) {
                        xh18s = "null";
                    } else {
                        xh18s = "'" + xh18 + "'";
                    }
                    if ((xh19 + "") == null || ("").equals((xh19 + ""))) {
                        xh19s = "null";
                    } else {
                        xh19s = "'" + xh19 + "'";
                    }
                    if ((xh20 + "") == null || ("").equals((xh20 + ""))) {
                        xh20s = "null";
                    } else {
                        xh20s = "'" + xh20 + "'";
                    }
                    if ((xh21 + "") == null || ("").equals((xh21 + ""))) {
                        xh21s = "null";
                    } else {
                        xh21s = "'" + xh21 + "'";
                    }
                    if ((xh22 + "") == null || ("").equals((xh22 + ""))) {
                        xh22s = "null";
                    } else {
                        xh22s = "'" + xh22 + "'";
                    }
                    if ((xh23 + "") == null || ("").equals((xh23 + ""))) {
                        xh23s = "null";
                    } else {
                        xh23s = "'" + xh23 + "'";
                    }
                    if ((xh24 + "") == null || ("").equals((xh24 + ""))) {
                        xh24s = "null";
                    } else {
                        xh24s = "'" + xh24 + "'";
                    }
                    if ((xh25 + "") == null || ("").equals((xh25 + ""))) {
                        xh25s = "null";
                    } else {
                        xh25s = "'" + xh25 + "'";
                    }
                    String id = CommUtils.getUUID();
                    String tableHead = ShiroKit.getUser().getAbbreviation();
                    id = id + tableHead;
                    Random rand = new Random();
                    int randomNumber = rand.nextInt(999999);
                    id = id + randomNumber;
                    String ageRange = "";
                    if (!"null".equals(xh4s)) {
                        for (int i = 0; i < 11; i++) {
                            if (Integer.parseInt(xh4s.substring(1, xh4s.length() - 3)) >= (i * 10) &&
                                    Integer.parseInt(xh4s.substring(1, xh4s.length() - 3)) <= (i * 10 + 9)) {
                                ageRange = (i * 10) + "-" + (i * 10 + 9);
                            }
                        }
                    }

                /*    if (!"null".equals(xh14s)) {
                        if (!(xh6s.substring(0, 11)).equals(xh13s.substring(0, 11))) {
                            String sql2 = "SELECT *,COUNT(CASE a.deal_status WHEN '已成交' THEN 0 END) AS consumption\n" +
                                    "from " + this.getTableName() + " a where a.first_visit_date<=left(a.arrival_time,10)\n" +
                                    "and a.arrival_time<='2018-07-01' and a.reservations_id= " + xh1s + " group by a.reservations_id\n" +
                                    "HAVING consumption>0";
                            List<CustomerInfo> list2 = sqlMapper.selectList(sql2, CustomerInfo.class);
                            if (list2.size() > 0) {
                                customerStatus = "复诊";
                            } else {
                                customerStatus = "新客二次";
                            }
                        } else {
                            customerStatus = "新客首次";
                        }
                    } else {
                        customerStatus = "预约";
                    }
*/

                    String selectsql1 = "select * from " + getTableName() + " where customer_phone='" + xh5s + "' and  id22 is null ;";
                    List<CustomerInfo> customerInfosList1 = sqlMapper.selectList(selectsql1, CustomerInfo.class);
                    if (customerInfosList1.size() == 0) {
                        String selectsql = "select * from " + getTableName() + " where id='" + id + "';";
                        List<CustomerInfo> customerInfosList = sqlMapper.selectList(selectsql, CustomerInfo.class);
                        if (customerInfosList.size() > 0) {
                            id = randomNumber + id.substring(0, 40);
                        }
                        String sql1 = "select id from  reservations_info where identification_number=" + xh1s + " limit 0,1";
                        CustomerInfo c = sqlMapper.selectOne(sql1, CustomerInfo.class);
                        if (c != null) {
                            c.setReservationsId(c.getId());
                            String customerStatus = service.getCustomerStatus(c);
                            String sql = "insert into " + getTableName() + " " +
                                    "( id,reservations_id,id22,medical_record_number,customer_name,customer_sex,customer_age,customer_phone,filing_time," +
                                    "one_channel,two_channel,thr_channel,one_medium,two_medium,thr_medium,"
                                    + "first_visit_date,appointment_pay_service,entry_clerk,appointment_time,arrival_time,doctor,deal_status,field_tc" +
                                    ",customer_status, age_range,arrival_store,arrival_status,status) VALUES " +
                                    "('" + id + "','" + c.getId() + "','12月9日导入', " + xh1s + " , " + xh2s + " , " + xh3s + " " +
                                    ", " + xh4s + " ,  '" + xh5s + "'  , '" + xh6s.substring(0, 11) + "' " +
                                    ", " + xh7s + " , " + xh8s + " , " + xh9s + ", " + xh7s + " , " + xh8s + " , " + xh9s + " , " + xh10s + " , " + xh11s + " ," +
                                    " " + xh12s + " , '" + xh13s + "' , " + xh14s + " , " + xh15s + " , " + xh16s + "," + xh17s + ",'" + customerStatus + "','"
                                    + ageRange + "' ,'维尔店','5','到店');";
                            sqlMapper.insert(sql);
                        } else {
                            String sql2 = "select id from  reservations_info where customer_phone='" + xh5s + "' limit 0,1";
                            CustomerInfo c2 = sqlMapper.selectOne(sql2, CustomerInfo.class);
                            if (c2 != null) {
                                c2.setReservationsId(c2.getId());
                                String customerStatus = service.getCustomerStatus(c2);
                                String sql = "insert into " + getTableName() + " " +
                                        "( id,reservations_id,id22,medical_record_number,customer_name,customer_sex,customer_age,customer_phone,filing_time," +
                                        "one_channel,two_channel,thr_channel,one_medium,two_medium,thr_medium,"
                                        + "first_visit_date,appointment_pay_service,entry_clerk,appointment_time,arrival_time,doctor,deal_status,field_tc" +
                                        ",customer_status, age_range,arrival_store,arrival_status,status) VALUES " +
                                        "('" + id + "','" + c2.getId() + "','12月9日导入', " + xh1s + " , " + xh2s + " , " + xh3s + " " +
                                        ", " + xh4s + " ,  '" + xh5s + "'  , '" + xh6s.substring(0, 11) + "' " +
                                        ", " + xh7s + " , " + xh8s + " , " + xh9s + ", " + xh7s + " , " + xh8s + " , " + xh9s + " , " + xh10s + " , " + xh11s + " ," +
                                        " " + xh12s + " , '" + xh13s + "' , " + xh14s + " , " + xh15s + " , " + xh16s + "," + xh17s + ",'" + customerStatus + "','"
                                        + ageRange + "' ,'维尔店','5','到店');";
                                sqlMapper.insert(sql);
                            } else {
                                String customerStatus = "新客首次";
                                String sql = "insert into " + getTableName() + " " +
                                        "( id,reservations_id,id22,medical_record_number,customer_name,customer_sex,customer_age,customer_phone,filing_time," +
                                        "one_channel,two_channel,thr_channel,one_medium,two_medium,thr_medium,"
                                        + "first_visit_date,appointment_pay_service,entry_clerk,appointment_time,arrival_time,doctor,deal_status,field_tc" +
                                        ",customer_status, age_range,arrival_store,arrival_status,status) VALUES " +
                                        "('" + id + "','客户表没有','12月9日导入', " + xh1s + " , " + xh2s + " , " + xh3s + " " +
                                        ", " + xh4s + " ,  '" + xh5s + "'  , '" + xh6s.substring(0, 11) + "' " +
                                        ", " + xh7s + " , " + xh8s + " , " + xh9s + ", " + xh7s + " , " + xh8s + " , " + xh9s + " , " + xh10s + " , " + xh11s + " ," +
                                        " " + xh12s + " , '" + xh13s + "' , " + xh14s + " , " + xh15s + " , " + xh16s + "," + xh17s + ",'" + customerStatus + "','"
                                        + ageRange + "' ,'维尔店','5','到店');";
                                sqlMapper.insert(sql);
                            }
                        }
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }


    /** @RequestMapping(value = "/upload")
    public String importTestQuestions(@Valid MultipartFile file) {
    if (!StringUtil.isEmpty(file)) {
    Random random = new Random();
    String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
    String FileInput = realPath + "\\" + file.getOriginalFilename();
    try {
    FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
    InputStream is = new FileInputStream(FileInput);
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    // 循环工作表Sheet
    Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
    Row hssfRow0 = hssfSheet.getRow(0);
    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
    Row hssfRow = hssfSheet.getRow(rowNum);
    if (hssfRow == null) {
    continue;
    }
    Cell xh1 = hssfRow.getCell(0);
    Cell xh2 = hssfRow.getCell(1);
    Cell xh3 = hssfRow.getCell(2);
    Cell xh4 = hssfRow.getCell(3);
    Cell xh5 = hssfRow.getCell(4);
    Cell xh6 = hssfRow.getCell(5);
    Cell xh7 = hssfRow.getCell(6);
    Cell xh8 = hssfRow.getCell(7);
    Cell xh9 = hssfRow.getCell(8);
    Cell xh10 = hssfRow.getCell(9);
    Cell xh11 = hssfRow.getCell(10);
    Cell xh12 = hssfRow.getCell(11);
    Cell xh13 = hssfRow.getCell(12);
    Cell xh14 = hssfRow.getCell(13);
    Cell xh15 = hssfRow.getCell(14);
    Cell xh16 = hssfRow.getCell(15);
    Cell xh17 = hssfRow.getCell(16);
    Cell xh18 = hssfRow.getCell(17);
    Cell xh19 = hssfRow.getCell(18);
    Cell xh20 = hssfRow.getCell(19);
    Cell xh21 = hssfRow.getCell(20);
    Cell xh22 = hssfRow.getCell(21);
    Cell xh23 = hssfRow.getCell(22);
    Cell xh24 = hssfRow.getCell(23);
    Cell xh25 = hssfRow.getCell(24);
    String xh1s = "";
    String xh2s = "";
    String xh3s = "";
    String xh4s = "";
    String xh5s = "";
    String xh6s = "";
    String xh7s = "";
    String xh8s = "";
    String xh9s = "";
    String xh10s = "";
    String xh11s = "";
    String xh12s = "";
    String xh13s = "";
    String xh14s = "";
    String xh15s = "";
    String xh16s = "";
    String xh17s = "";
    String xh18s = "";
    String xh19s = "";
    String xh20s = "";
    String xh21s = "";
    String xh22s = "";
    String xh23s = "";
    String xh24s = "";
    String xh25s = "";
    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
    xh1s = "null";
    } else {
    xh1s = "'" + xh1 + "'";
    }
    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
    xh2s = "null";
    } else {
    xh2s = "'" + xh2 + "'";
    }
    if ((xh3) == null || ("").equals(xh3)) {
    xh3s = "null";
    } else {
    if ("男".equals((xh3 + ""))) {
    xh3s = "'" + 1 + "'";
    } else if ("女".equals((xh3 + ""))) {
    xh3s = "'" + 2 + "'";
    } else if ("未知".equals((xh3 + ""))) {
    xh3s = "null";
    }
    }
    if ((xh4) == null || ("").equals(xh4)) {
    xh4s = "null";
    } else {
    xh4s = "'" + xh4 + "'";
    }
    if ((xh5) == null || ("").equals(xh5)) {
    xh5s = "null";
    } else {
    xh5s = "'" + xh5 + "'";
    }

    //                    Date d = xh6.getDateCellValue();
    //                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    //                    String str2  = formater.format(d);
    //                    System.out.println(str2);

    //                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    //                    String str = sdf.format(HSSFDateUtil.getJavaDate(xh6.getNumericCellValue())).toString();
    //                    System.out.println(str);
    if ((xh6 + "") == null || ("").equals((xh6 + ""))) {
    xh6s = "null";
    } else {
    xh6s = "" + xh6 + "";
    }
    if ((xh7 + "") == null || ("").equals((xh7 + ""))) {
    xh7s = "null";
    } else {
    xh7s = "'" + xh7 + "'";
    }
    if ((xh8 + "") == null || ("").equals((xh8 + ""))) {
    xh8s = "null";
    } else {
    xh8s = "'" + xh8 + "'";
    }
    if ((xh9 + "") == null || ("").equals((xh9 + ""))) {
    xh9s = "null";
    } else {
    xh9s = "'" + xh9 + "'";
    }
    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
    xh10s = "null";
    } else {
    xh10s = "'" + xh10 + "'";
    }
    if ((xh11) == null || ("").equals(xh11)) {
    xh11s = "null";
    } else {
    xh11s = "'" + xh11 + "'";
    }
    if ((xh12 + "") == null || ("").equals((xh12 + ""))) {
    xh12s = "null";
    } else {
    xh12s = "'" + xh12 + "'";
    }
    if ((xh13 + "") == null || ("").equals((xh13 + ""))) {
    xh13s = "null";
    } else {
    xh13s = "" + xh13 + "";
    }
    if ((xh14) == null || ("").equals((xh14))) {
    xh14s = "null";
    } else {
    xh14s = "'" + xh14 + "'";
    }
    if ((xh15 + "") == null || ("").equals((xh15 + ""))) {
    xh15s = "null";
    } else {
    xh15s = "'" + xh15 + "'";
    }
    if ((xh16 + "") == null || ("").equals((xh16 + ""))) {
    xh16s = "null";
    } else {
    xh16s = "'" + xh16 + "'";
    }
    if ((xh17 + "") == null || ("").equals((xh17 + ""))) {
    xh17s = "null";
    } else {
    xh17s = "'" + xh17 + "'";
    }
    if ((xh18 + "") == null || ("").equals((xh18 + ""))) {
    xh18s = "null";
    } else {
    xh18s = "'" + xh18 + "'";
    }
    if ((xh19 + "") == null || ("").equals((xh19 + ""))) {
    xh19s = "null";
    } else {
    xh19s = "'" + xh19 + "'";
    }
    if ((xh20 + "") == null || ("").equals((xh20 + ""))) {
    xh20s = "null";
    } else {
    xh20s = "'" + xh20 + "'";
    }
    if ((xh21 + "") == null || ("").equals((xh21 + ""))) {
    xh21s = "null";
    } else {
    xh21s = "'" + xh21 + "'";
    }
    if ((xh22 + "") == null || ("").equals((xh22 + ""))) {
    xh22s = "null";
    } else {
    xh22s = "'" + xh22 + "'";
    }
    if ((xh23 + "") == null || ("").equals((xh23 + ""))) {
    xh23s = "null";
    } else {
    xh23s = "'" + xh23 + "'";
    }
    if ((xh24 + "") == null || ("").equals((xh24 + ""))) {
    xh24s = "null";
    } else {
    xh24s = "'" + xh24 + "'";
    }
    if ((xh25 + "") == null || ("").equals((xh25 + ""))) {
    xh25s = "null";
    } else {
    xh25s = "'" + xh25 + "'";
    }
    String id = CommUtils.getUUID();
    String tableHead = ShiroKit.getUser().getAbbreviation();
    id = id + tableHead;
    Random rand = new Random();
    int randomNumber = rand.nextInt(999999);
    id = id + randomNumber;
    String ageRange = "";
    if (!"null".equals(xh4s)) {
    for (int i = 0; i < 11; i++) {
    if (Integer.parseInt(xh4s.substring(1, xh4s.length() - 3)) >= (i * 10) &&
    Integer.parseInt(xh4s.substring(1, xh4s.length() - 3)) <= (i * 10 + 9)) {
    ageRange = (i * 10) + "-" + (i * 10 + 9);
    }
    }
    }

    //                    String customerStatus = "";
    //                    if (!"null".equals(xh14s)) {
    //                        if (!(xh6s.substring(0, 11)).equals(xh13s.substring(0, 11))) {
    //                            String sql2 = "SELECT *,COUNT(CASE a.deal_status WHEN '已成交' THEN 0 END) AS consumption\n" +
    //                                    "from " + this.getTableName() + " a where a.first_visit_date<=left(a.arrival_time,10)\n" +
    //                                    "and a.arrival_time<='2018-07-01' and a.reservations_id= " + xh1s + " group by a.reservations_id\n" +
    //                                    "HAVING consumption>0";
    //                            List<CustomerInfo> list2 = sqlMapper.selectList(sql2, CustomerInfo.class);
    //                            if (list2.size() > 0) {
    //                                customerStatus = "复诊";
    //                            } else {
    //                                customerStatus = "新客二次";
    //                            }
    //                        } else {
    //                            customerStatus = "新客首次";
    //                        }
    //                    } else {
    //                        customerStatus = "预约";
    //                    }

    String customerStatus = "";
    if (!"null".equals(xh14s)) {
    if (!(xh6s.substring(0, 11)).equals(xh13s.substring(0, 11))) {
    String sql2 = "SELECT *,COUNT(CASE a.deal_status WHEN '已成交' THEN 0 END) AS consumption\n" +
    "from " + this.getTableName() + " a where a.first_visit_date<=left(a.arrival_time,10)\n" +
    "and a.arrival_time<='2018-07-01' and a.reservations_id= " + xh1s + " group by a.reservations_id\n" +
    "HAVING consumption>0";
    List<CustomerInfo> list2 = sqlMapper.selectList(sql2, CustomerInfo.class);
    if (list2.size() > 0) {
    customerStatus = "复到";
    } else {
    customerStatus = "初到";
    }
    } else {
    customerStatus = "初到";
    }
    } else {
    customerStatus = "预约";
    }

    String selectsql = "select * from " + getTableName() + " where id='" + id + "';";
    List<CustomerInfo> customerInfosList = sqlMapper.selectList(selectsql, CustomerInfo.class);
    if (customerInfosList.size() > 0) {
    id = randomNumber + id.substring(0, 40);
    }


    String sql1 = "select id from " + getTableName() + " where customer_phone =" + xh5s + " and left(arrival_time,10)=" + xh14s.substring(0, 11) + "' limit 0,1;";
    CustomerInfo customerInfos2 = sqlMapper.selectOne(sql1, CustomerInfo.class);
    if (customerInfos2 != null) {
    String sql = "update  " + getTableName() + " a set a.medical_record_number=" + xh1s + " where id='" + customerInfos2.getId() + "'";
    sqlMapper.update(sql);
    }
    else{
    String sql6 = "select id from " + getTableName() + " where customer_name =" + xh2s + " and left(arrival_time,10)=" + xh14s.substring(0, 11) + "' limit 0,1;";
    CustomerInfo infos2 = sqlMapper.selectOne(sql6, CustomerInfo.class);
    if (infos2 != null) {
    String sql8 = "update  " + getTableName() + " a set a.medical_record_number=" + xh1s + " where id='" + infos2.getId() + "'";
    sqlMapper.update(sql8);
    }else{
    String  sql2="select id from  reservations_info where identification_number="+xh1s+" limit 0,1";
    CustomerInfo c= sqlMapper.selectOne(sql2, CustomerInfo.class);
    if(c!=null){
    c.setReservationsId(c.getId());
    String sql4 = "insert into " + getTableName() + " " +
    "( id,reservations_id,medical_record_number,customer_name,customer_sex,customer_age,customer_phone,filing_time," +
    "one_channel,two_channel,thr_channel,one_medium,two_medium,thr_medium,"
    + "first_visit_date,appointment_pay_service,entry_clerk,appointment_time,arrival_time,doctor,field_tc" +
    ",customer_status, age_range,arrival_store) VALUES " +
    "('" + id + "', '" + c.getId() + "'," + xh1s + " , " + xh2s + " , " + xh3s + " " +
    ", " + xh4s + " ,  " + xh5s + "  , '" + xh6s.substring(0, 11) + "' " +
    ", " + xh7s + " , " + xh8s + " , " + xh9s + ", " + xh7s + " , " + xh8s + " , " + xh9s + " , " + xh10s + " , " + xh11s + " ," +
    " " + xh12s + " , '" + xh13s + "' , " + xh14s + " , " + xh15s + " , " + xh16s + ",'" + customerStatus + "','"
    + ageRange + "' ,'斯美尔店');";
    sqlMapper.insert(sql4);
    }else{
    String  sql3="select id from  reservations_info where customer_phone="+xh5s+" limit 0,1";
    CustomerInfo c2= sqlMapper.selectOne(sql3, CustomerInfo.class);
    if(c2!=null){
    c2.setReservationsId(c2.getId());
    String sql5 = "insert into " + getTableName() + " " +
    "( id,reservations_id,medical_record_number,customer_name,customer_sex,customer_age,customer_phone,filing_time," +
    "one_channel,two_channel,thr_channel,one_medium,two_medium,thr_medium,"
    + "first_visit_date,appointment_pay_service,entry_clerk,appointment_time,arrival_time,doctor,field_tc" +
    ",customer_status, age_range,arrival_store) VALUES " +
    "('" + id + "', '" + c2.getId() + "'," + xh1s + " , " + xh2s + " , " + xh3s + " " +
    ", " + xh4s + " ,  " + xh5s + "  , '" + xh6s.substring(0, 11) + "' " +
    ", " + xh7s + " , " + xh8s + " , " + xh9s + ", " + xh7s + " , " + xh8s + " , " + xh9s + " , " + xh10s + " , " + xh11s + " ," +
    " " + xh12s + " , '" + xh13s + "' , " + xh14s + " , " + xh15s + " , " + xh16s + ",'" + customerStatus + "','"
    + ageRange + "' ,'斯美尔店');";
    sqlMapper.insert(sql5);
    }else{
    customerStatus="新客首次";
    String sql5 = "insert into " + getTableName() + " " +
    "( id,reservations_id,medical_record_number,customer_name,customer_sex,customer_age,customer_phone,filing_time," +
    "one_channel,two_channel,thr_channel,one_medium,two_medium,thr_medium,"
    + "first_visit_date,appointment_pay_service,entry_clerk,appointment_time,arrival_time,doctor,field_tc" +
    ",customer_status, age_range,arrival_store) VALUES " +
    "('" + id + "', '客户表没有'," + xh1s + " , " + xh2s + " , " + xh3s + " " +
    ", " + xh4s + " ,  " + xh5s + "  , '" + xh6s.substring(0, 11) + "' " +
    ", " + xh7s + " , " + xh8s + " , " + xh9s + ", " + xh7s + " , " + xh8s + " , " + xh9s + " , " + xh10s + " , " + xh11s + " ," +
    " " + xh12s + " , '" + xh13s + "' , " + xh14s + " , " + xh15s + " , " + xh16s + ",'" + customerStatus + "','"
    + ageRange + "' ,'斯美尔店');";
    sqlMapper.insert(sql5);
    }
    }
    }
    }
    }
    } catch (IOException e) {
    e.printStackTrace();
    }
    return PREFIX + "testClose.html";
    } else {
    return PREFIX + "testClose.html";
    }
     */
    /**
     * 统计查询
     */
    @RequestMapping(value = "/total", method = RequestMethod.GET)
    @ResponseBody
    public List<ConsultantInfo> changeTotal(String appointmentPayService, String startArrivalTime, String endArrivalTime, String oneChannelValue, String twoChannelValue, String thrChannelValue, String reach, String dealStatus) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        String contitionInfo = " AND t.customer_status != '作废' and t.arrival_status!=1 ";
        if (StringUtils.isNotBlank(startArrivalTime)) {
            contitionInfo += " and t.arrival_time >= '" + startArrivalTime + "' ";
        }
        if (StringUtils.isNotBlank(endArrivalTime)) {
            contitionInfo += " and t.arrival_time <= '" + endArrivalTime + "' ";
        }

        if (StringUtil.isNotEmpty(oneChannelValue) && !"请选择".equals(oneChannelValue)) {
            String oneChannel = "";
            String oneChannel2[] = oneChannelValue.split(",");
            if (oneChannel2.length == 1) {
                oneChannel = oneChannel2[0];
            } else {
                for (int i = 0; i < oneChannel2.length; i++) {
                    if (i == (oneChannel2.length - 1)) {
                        oneChannel += "'" + oneChannel2[i];
                    } else if (i == 0) {
                        oneChannel += oneChannel2[i] + "'" + ",";
                    } else {
                        oneChannel += "'" + oneChannel2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.one_channel in ( '" + oneChannel + "') ";
        }

        if (StringUtil.isNotEmpty(twoChannelValue) && !"请选择".equals(twoChannelValue)) {
            String twoChannel = "";
            String twoChannel2[] = twoChannelValue.split(",");
            if (twoChannel2.length == 1) {
                twoChannel = twoChannel2[0];
            } else {
                for (int i = 0; i < twoChannel2.length; i++) {
                    if (i == (twoChannel2.length - 1)) {
                        twoChannel += "'" + twoChannel2[i];
                    } else if (i == 0) {
                        twoChannel += twoChannel2[i] + "'" + ",";
                    } else {
                        twoChannel += "'" + twoChannel2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.two_channel  in ('" + twoChannel + "') ";
        }
        if (StringUtil.isNotEmpty(thrChannelValue) && !"请选择".equals(thrChannelValue)) {
            String thrChannel = "";
            String thrChannel2[] = thrChannelValue.split(",");
            if (thrChannel2.length == 1) {
                thrChannel = thrChannel2[0];
            } else {
                for (int i = 0; i < thrChannel2.length; i++) {
                    if (i == (thrChannel2.length - 1)) {
                        thrChannel += "'" + thrChannel2[i];
                    } else if (i == 0) {
                        thrChannel += thrChannel2[i] + "'" + ",";
                    } else {
                        thrChannel += "'" + thrChannel2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.thr_channel in ('" + thrChannel + "') ";
        }

        if (StringUtils.isNotBlank(dealStatus)) {
            if ("已成交".equals(dealStatus)) {
                contitionInfo += " and t.deal_status = '已成交' ";
            } else {
                contitionInfo += " and t.deal_status != '已成交' ";
            }
        }
        if (StringUtils.isNotBlank(appointmentPayService)) {
            String appointmentPayService2 = "";
            String appointmentPayServices[] = appointmentPayService.split(",");
            if (appointmentPayServices.length == 1) {
                appointmentPayService2 = appointmentPayServices[0];
            } else {
                for (int i = 0; i < appointmentPayServices.length; i++) {
                    if (i == (appointmentPayServices.length - 1)) {
                        appointmentPayService2 += "'" + appointmentPayServices[i];
                    } else if (i == 0) {
                        appointmentPayService2 += appointmentPayServices[i] + "'" + ",";
                    } else {
                        appointmentPayService2 += "'" + appointmentPayServices[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.appointment_pay_service in ('" + appointmentPayService2 + "') ";
        }
        if (StringUtils.isNotBlank(reach)) {
            contitionInfo += " and (t.customer_name like CONCAT('%','" + reach + "','%') or t.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " t.wechat_number like CONCAT('%','" + reach + "','%') or t.medical_record_number like CONCAT('%','" + reach + "','%')) ";
        }


        String sql = "select count(*) as countNum,sum(actual_account_balance) as actualAccountBalanceNum from " + tableName + " t  where 1=1 " + contitionInfo +

                "union all select count(*) as countNum,sum(actual_account_balance) as actualAccountBalanceNum from " + tableName + " t  where  t.xcustomer_status in ('初到','初治','流失') " + contitionInfo +

                " union all select count(*) as countNum,sum(actual_account_balance) as actualAccountBalanceNum  from " + tableName + " t  where t.xcustomer_status='初到洁牙'" + contitionInfo +

                " union all select count(*) as countNum ,sum(actual_account_balance) as actualAccountBalanceNum from " + tableName + " t  where t.xcustomer_status='初治'" + contitionInfo +

                " union all select count(*) as countNum,sum(actual_account_balance) as actualAccountBalanceNum  from " + tableName + " t  where t.xcustomer_status in ('复到','复到初治')" + contitionInfo +

                " union all select count(*) as countNum ,sum(actual_account_balance) as actualAccountBalanceNum from " + tableName + " t  where t.xcustomer_status='复到洁牙'" + contitionInfo +

                " union all select count(*) as countNum,sum(actual_account_balance) as actualAccountBalanceNum  from " + tableName + " t  where t.xcustomer_status='复到初治'" + contitionInfo +

                " union all select count(*) as countNum ,sum(actual_account_balance) as actualAccountBalanceNum from " + tableName + " t  where t.xcustomer_status='流失'" + contitionInfo+

                " union all select count(*) as countNum ,sum(actual_account_balance) as actualAccountBalanceNum from " + tableName + " t  where t.xcustomer_status='再消'" + contitionInfo;

        List<ConsultantInfo> infoList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return infoList;
    }

    @RequestMapping(value = "/customerExport")
    public void reservationsExport(@RequestParam(required = false) String reach,
                                   @RequestParam(required = false) String startArrivalTime,
                                   @RequestParam(required = false) String endArrivalTime,
                                   @RequestParam(required = false) String fieldTc,
                                   @RequestParam(required = false) String appointmentPayService,
                                   @RequestParam(required = false) String oneChannel,
                                   @RequestParam(required = false) String twoChannel,
                                   @RequestParam(required = false) String thrChannel,
                                   @RequestParam(required = false) String backVisit,
                                   @RequestParam(required = false) String customerStatus,
                                   @RequestParam(required = false) String xcustomerStatus,
                                   @RequestParam(required = false) String dealStatus, HttpServletResponse response) {
        try {
            SearchCustomerInfo searchInfo = new SearchCustomerInfo();
            searchInfo.setReach(reach);
            searchInfo.setFieldTc(fieldTc);
            searchInfo.setBackVisit(backVisit);
            searchInfo.setStartArrivalTime(startArrivalTime);
            searchInfo.setEndArrivalTime(endArrivalTime);
            searchInfo.setOneChannel(oneChannel);
            searchInfo.setTwoChannel(twoChannel);
            searchInfo.setThrChannel(thrChannel);
            searchInfo.setDealStatus(dealStatus);
            searchInfo.setAppointmentPayService(appointmentPayService);
            searchInfo.setCustomerStatus(customerStatus);
            searchInfo.setXcustomerStatus(xcustomerStatus);
            List<CustomerInfo> list = service.customerExport(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 20; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("客户状态");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("病历号");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("姓名");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("手机号");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("咨询项目");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("首次到店时间");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("到店时间");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("tc");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("医生");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("医助/护士");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("成交状态");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("成交金额");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("欠费");
            cell.setCellStyle(style);


            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                CustomerInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                String customerName = "";
                if (map.getCustomerName() != null) {
                    customerName = map.getCustomerName();
                }
                String sex = map.getSex();
                int customerAge = 0;
                if (map.getCustomerAge() != null) {
                    customerAge = map.getCustomerAge();
                }
                String medicalRecordNumber = map.getMedicalRecordNumber();
                appointmentPayService = map.getAppointmentPayService();
                String arrivalTime = map.getArrivalTime();

                customerStatus = map.getCustomerStatus();
                xcustomerStatus = map.getXcustomerStatus();

                String customerPhone = "";
                if (map.getCustomerPhone() != null) {
                    customerPhone = map.getCustomerPhone();
                }
                sex = map.getSex();
                if (map.getCustomerAge() != null) {
                    customerAge = map.getCustomerAge();
                }
                fieldTc = map.getFieldTc();
                oneChannel = map.getOneChannel();
                twoChannel = map.getTwoChannel();
                thrChannel = map.getThrChannel();
                String doctor = map.getDoctor();
                String nurse = map.getNurse();
                if (doctor != null && doctor != "") {
                    doctor = doctor.replaceAll(",", " ");
                }
                if (nurse != null && nurse != "") {
                    nurse = nurse.replaceAll(",", " ");
                }
                String filingTime = map.getFilingTime();
                String firstVisitDate = map.getFirstVisitDate();
                dealStatus = map.getDealStatus();
                String actualAccountBalances = map.getActualAccountBalances();
                if (StringUtil.isEmpty(actualAccountBalances)) {
                    actualAccountBalances = "0.00";
                }
                String accountArrearss = map.getAccountArrearss();
                if (StringUtil.isEmpty(accountArrearss)) {
                    accountArrearss = "0.00";
                }
                String id = map.getId();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 17; j++) {
                    row.createCell(j).setCellStyle(style);
                }
//                row.getCell(0).setCellValue(customerStatus);
                row.getCell(0).setCellValue(xcustomerStatus);
                row.getCell(1).setCellValue(medicalRecordNumber);
                row.getCell(2).setCellValue(customerName);
                row.getCell(3).setCellValue(sex);
                row.getCell(4).setCellValue(customerAge);
                row.getCell(5).setCellValue(customerPhone);
                row.getCell(6).setCellValue(appointmentPayService);
                row.getCell(7).setCellValue(oneChannel);
                row.getCell(8).setCellValue(twoChannel);
                row.getCell(9).setCellValue(thrChannel);
                row.getCell(10).setCellValue(firstVisitDate);
                row.getCell(11).setCellValue(arrivalTime);
                row.getCell(12).setCellValue(fieldTc);
                row.getCell(13).setCellValue(doctor);
                row.getCell(14).setCellValue(nurse);
                row.getCell(15).setCellValue(dealStatus);
                row.getCell(16).setCellValue(actualAccountBalances);
                row.getCell(17).setCellValue(accountArrearss);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
